Xcel Formula # rows With value X in colF AND Value A or B in colJ

C

Cat

I'm trying to find a way to build a conditional formula to return the
following values:

Count when Column F = X AND when Column J = A or B.
 
B

Bondi

Cat said:
I'm trying to find a way to build a conditional formula to return the
following values:

Count when Column F = X AND when Column J = A or B.
Click to expand...

Hi Cat,

Here is one way of doing it:

=SUMPRODUCT(--(F1:F10="X"),--(J1:J10="A"))+SUMPRODUCT(--(F1:F10="X"),--(J1:J10="B"))

Note that you can not use the entire rows in the formula but have to
specifie a range.

Regards,
Bondi
 
D

Domenic

Try...

=SUMPRODUCT(--(F2:F100="X"),--ISNUMBER(MATCH(J2:J100,{"A","B"},0)))

or

=SUMPRODUCT(--(F2:F100="X"),--ISNUMBER(MATCH(J2:J100,M2:M3,0)))

....where M2 and M3 contain A and B, respectively.

Hope this helps!
 
Top