Countif only if values in two ranges are found

**agni452** · 06-26-2013, 06:40 AM

I have two columns A (items) and B (store names). I need to check the ranges A:A and B:B and count how many times a certain item appears in column A AND a certain store appears in column B. For example I want to see how many times the item "plates" are listed for the store "walmart". If I had the following data

napkins	walmart
plates	walmart
forks	target
plates	walmart
spoons	target

I should get an answer of 2 because "plates" AND "walmart" appear in the same row twice. If I asked for "spoons" and "target" I would get 1, and if I asked for "spoons" and "walmart" I would get 0. Is there are formula that I can use to check the same row in two different columns for certain values and count the rows that have those values?

**Ace_XL** · 06-26-2013, 06:48 AM

Try

=SUMPRODUCT((A1:A5="plates")*(B1:B5="walmart"))
or
=COUNTIFS(A:A,"plates",B:B,"walmart")

You can also use cell references instead of text

**agni452** · 06-26-2013, 07:31 AM

Originally Posted by Ace_XL

Try

=COUNTIFS(A:A,"plates",B:B,"walmart")

I tried that earlier and it didn't return the correct answers. Now that I see that I was on the right track, I looked back to see what I did wrong, and it turns out I needed to add a few '$' to the formula since I had dragged it to other columns. It was looking at the wrong columns to compare!

Thanks for the help!