# Microsoft Office Application Help - Excel Help forum > Excel General >  >  CountIFS using an OR function

## Maudise

Hi,

I have tried searching the forums and google for an answer to do this but I cannot seem to find a solution to my problem.

I am using Excel 2007 and looking at a large amount of data.  To simplify the problem I have built the Table below
   A             B
1	Jack
1	James
2	John
2	jack
2	james
1	james

I want to use Countifs to display the amount of times that Jack OR James are displayed in B AND 1 is displayed in Column 1.

I have tried to do this through, = countifs($A:$A, "=1", $B:$B, {"Jack", "James"}) 
however the result it returns only reviews the first in the array, in this case, Jack.

Is there any way to use a nested OR function within a Countifs function without having to create a separate column and using =if(OR(A1="Jack", A1="James"),1,0) and then using Countifs with =countifs($A:$A, "=1", $C:$C,1) as the data set I am using is very large and this would be very time consuming.

Any / all help would be appreciated!

Many thanks

Maudise

----------


## NBVC

COUNTIFS is by nature an AND construct... 

To do an OR...use Sumproduct

e.g.
=SUMPRODUCT(((A1:A100="JACK")+(A1:A100="James"))*(C1:C100=1))

or

=SUMPRODUCT((A1:A100={"JACK","James"})*(C1:C100=1))

or add 2 individual Countifs() together

=COUNTIFS(A1:A100,"Jack",C1:C100,1)+COUNTIFS(A1:A100,"James",C1:C100,1)

or even:

=SUM(COUNTIFS(A1:A100,{"Jack","James"},C1:C100,1))

----------


## ChemistB

SUMPRODUCT Should work for you.  Try this;

=SUMPRODUCT(--($A$1:$A$10=1),($B$1:$B$10="Jack")+($B$1:$B$10="John"))

Does that work for you?

----------


## WinteE

Hi Maudise,

You can do this by using =SUMPRODUCT()

=SUMPRODUCT((A1:A6=E1)*(B1:B6=D1)*1), where cell E1 contains a number and cell D1 a name.

Erik

----------


## Maudise

Hi All,

Thanks for the prompt responses! The solutions do work, perhaps I over simplified it though.  The countifs has several criteria attached and so was trying to keep the formula's down to a manageable size using arrays and a function.  I have expanded it out just to see whether it can be adapted to be a little less unweidly, but I suspect further columns with sumproducts would be needed.

I want to see where Name = {"Jack", "James"}, Date >= 5th Jan AND <=7th Jan, Type = "Normal"

A	B	C	D
Date	Name	Outcome	Type
5th Jan	Jack	Success	Normal
6th Jan	James	Void	Normal
5th Jan	Jack	Success	Normal
5th Jan	James	Failure	Normal
6th Jan	John	Void	Normal
6th Jan	John	Success	Normal
7th Jan	John	Success	Normal
7th Jan	Jack	Success	Non-Standard
7th Jan	Jack	Failure	Normal
8th Jan	James	Failure	Non-Standard

The formula I am using at the minute is =countifs($A:$A, ">="&$E$2, $A:$A, "<"&$E$3, $C:$C, "<> Void",$D:$D, "=Normal", <PROBLEM OCCURS HERE> $B:$B, {"Jack", "James"})

The array for Jack / James in practice can get to 10 or so different variations and the spreadsheet is very large.  Is there any way of embedding them to amalgamate the OR and the AND functions to count or is it easiest to use the =if(or(B1="jack", B1="James"),1,0) and then adding in the extra column =1 function into the countifs.

But thank you for the Sumproduct answers, they're really useful for something else I've been working on!

Thanks
Muaidse

----------


## WinteE

Have a look on my site and find out about MLOOKUP.

----------


## DonkeyOte

> I want to see where Name = {"Jack", "James"}, Date >= 5th Jan AND <=7th Jan, Type = "Normal"
> 
> ...
> The formula I am using at the minute is 
> 
> =countifs($A:$A, ">="&$E$2, $A:$A, "<"&$E$3, $C:$C, "<> Void",$D:$D, "=Normal", <PROBLEM OCCURS HERE> $B:$B, {"Jack", "James"})
> 
> The array for Jack / James in practice can get to 10 or so different variations and the spreadsheet is very large.  Is there any way of embedding them to amalgamate the OR and the AND functions to count or is it easiest to use the =if(or(B1="jack", B1="James"),1,0) and then adding in the extra column =1 function into the countifs.



As NBVC has already shown you can use an inline array for the OR terms and encase within a SUM 




```
Please Login or Register  to view this content.
```


however you should note you are in effect performing multiple COUNTIFS and thus a single field denoting 1/0 would make sense - used in conjunction with a single COUNTIFS function (much more efficient than SUMPRODUCT).





> =SUMPRODUCT((A1:A6=E1)*(B1:B6=D1)*1)



@Erik, the *1 is superfluous given the multiplication of the Logical Arrays is already coercing the output.

----------

