Count number of rows meeting either criteria

Hi,

I'm having brain freeze here (maybe because it's 3 AM), but I'm trying to count how many rows with two different criteria. It' really the opposite of countifs. So in Column A let's say there are countries and in column B there are currencies. I want to know how many rows contain "USA" in column A OR "USD" in column B. If I wanted both then it could be:

Formula:

Please Login or Register  to view this content.

Thanks.

abousetta

this for the either

=Countif(A:A,"USA")+Countif(B:B,"USD")

sorry deleted

Maybe create a column C

=if(or(A1="USA",B1="USD","*","")

Then in D countif(C:C, "*")

Originally Posted by AlanY

this for the either

=Countif(A:A,"USA")+Countif(B:B,"USD")

That will double-count any rows for which the column A entry is "USA" and the column B entry is "USD", which I don't believe is what is required.

Originally Posted by nflsales

=sum(Countif(A:A,{"USA","USD"}))

That's only looking at column A.

Try:

=SUMPRODUCT(0+((A1:A10="USA")+(B1:B10="USD")>0))

Regards

I think AlanY will double count and NFLsales will only work if they're both in the one column

=sum(index((((a:a="usa")+(b:b="usd"))>0)*1,0))

You are right XOR LX,
By oversight it was happened

Originally Posted by nflsales

=sum(index((((a:a="usa")+(b:b="usd"))>0)*1,0))

?? You want to process a formula over two million cells?

Remember that, unlike COUNTIF(S)/SUMIF(S), formulas such as the above are forced to calculate over all cells within the ranges passed to it, whether technically beyond the last-used cell or not.

And why use a combination of SUM and INDEX when SUMPRODUCT is designed just for that purpose?

Regards

Thanks everyone. Sumproduct by XOR LX and Sum/Index by nflsales both work. Reps for everyone

Sumproduct does seem the most appropriate choice in this situation, but always good to know that multiple options exist. Another option that came to my mind was:

Formula:

Please Login or Register  to view this content.

but thought that there must be a better way.