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

[Guest]

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.

 

[Bondi]

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.

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

 

[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!