COUNTIF - comparing 2 columns

[arh1179]

Hi All,

I have looked through the blogs and have been unable to locate a formula that works properly (I'm assuming this is user error).


Scenario:

I am trying to count all the rows that match the following criteria.

If

Column A value = 1 and

Column B value = A

Then

Count the Row

Else

Do not count


I have used the 2 formulas below.

=SUMPRODUCT((A:A="1")+(B:B="A")) - this only seems to be counting the B column because when I use the same formula with a different value in the A column, I receive the same results as the previous formula (and I should not)


=COUNTIF(A:A,"1")+COUNTIF(B:B,"A") - this adds up both columns. So if there are 100 results from column A and 10 results from column B, it gives me a total of 110


Any help is greatly appreciated.

 

[OleMiss2010]

Try this forumula:

=COUNTIFS(A:A,"1",B:B,"A")

 

[Luke M]

Be careful about having the 1 in quotation marks. W/ quotation marks, you are saying you want a text string that is "1", not the number.


Also, if pre-2007, then this will also work:

=SUMPRODUCT(--(A1:A100=1),--(B1:B100="A"))

 

[OleMiss2010]

That's a good point. I just wasn't sure if they were using the "1" as a filler for some other text or if it was actually a numerical value.