CountIFS using an OR function

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

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))

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?

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

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

Originally Posted by Maudise

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

=SUM(COUNTIFS(....,{"value1","value2","value3",...,"valuen"}))

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).

Originally Posted by WinteE

=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.