COUNTIF function with multiple conditions 1

[Edge118]

I have two columns, A and B. I need to write a formula to count how many rows contain the value 2 in column A and the value 3 in column B.

I was messing around with the COUNTIF function, but it looks like you can only use one column of data for this function. Any help you can provide is appreciated.

Thanks,
Chris

"Pin me. Perl me."

Regards,

Chris

 

[Lilliabeth]

=SUMPRODUCT((A1:A65500=2)*(B1:B65500=3))

 

[questionmark1]

=COUNTIF(A:A;2)+COUNTIF(B:B;3)

;-)Regards, QM1

 

[bluedragon2]

QM1, Your function will give you the total number of 2's in Column A and the total number of 3's in Column B, but it will not give you the number of instances that were both Column A and B have a 2 and 3 respectively.

Lilliabeth's SUMPRODUCT function will count the number of rows which contain the 2 and 3.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[firefytr]

But that is not what was stated, Blue. Besides, if you wanted to include both columns, you could respecitively in each and still keep formula integrity. Plus QM1's solution will work in all versions of Excel, past and future.

-----------
Regards,
Zack Barresse

 

[bluedragon2]

My apologies, I assumed the problem:

I have two columns, A and B. I need to write a formula to count how many rows contain the value 2 in column A and the value 3 in column B.

meant to only count how many rows were there is a 2 in col a and a 3 in col b...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Lilliabeth]

Zack, the word "and" implies that both conditions must be true in order for the row to be counted, so you see, it was what was stated. I did not see the word "or" in the original post at all. It seems like a straight-forward, well-articulated question to me, one with an obvious answer, SUMPRODUCT. I have seen the same question/answer on this forum probably hundreds of times.

About a week ago, you posted a link to info about SUMPRODUCT in response to a similar question yourself, but today you seem to dislike this function because of version issues.

What is different about this question, or what has happened in the last week to change your mind about the use of SUMPRODUCT?

 

[firefytr]

Oh I do like Sumproduct, maybe that's even an understatement. The part I don't like is the last row bit. Using Excel 12 B2 (as I'm sure you know) there is over 1 million rows. Using the CountIf vs. Sumproduct function gives more functionality across multiple versions. I think the formula definitely has it's place though.

Btw, I certainly hope I wasn't coming off as rude to anybody, that definitely was not the intention.

-----------
Regards,
Zack Barresse

 

[Lilliabeth]

I think the original poster is smart enough to see that if his data spans beyond row 65500, he will need to adjust the formula accordingly.

Using the CountIf vs. Sumproduct function gives more functionality across multiple versions.

You are again talking about the last row thing, right? If there is something else, something more significant, spit it out. Share it.

Using the CountIf vs. Sumproduct function gives more functionality across multiple versions.

So you think people should be using COUNTIF instead of SUMPRODUCT for questions like this one?

 

[SkipVought]

Lilliabeth,

There is a valid issue regarding the upcoming release of Excel which will have more than 65,536 rows and previous versions of Excel that have less than 65,536 rows and the upward and downward compatability of formulas with respect to this.

Since SUMPRODUCT cannot support full column ranges like A:A, it has an inherent shortcoming that other formulas such as COUNTIF do not have. This must be taken onto account.

I, personally, use names ranges with column names in row 1, so I could care less about 65,536 or any other row limit with respect to SUMPRODUCT.

Skip,

_[red][/red]

 

[firefytr]

In cases like this, the speed of CountIf beats out a Sumproduct solution. CountIf's algorithm can't hardly be beat by any frankenstein we can come up with. So to answer your first question, I would say there is the "row thing" and the "efficiency thing". As for your second question, yes.

-----------
Regards,
Zack Barresse

 

[Lilliabeth]

Yes, I realize that this is a valid issue. I am asking if there is some other issue.

Using the CountIf vs. Sumproduct function gives more functionality across multiple versions.

Sounded to me like there is something else (something not so obvious). If there is, I'd like to know about it.

 

[bluedragon2]

I need some education, if this is a multiple criteria question, how does countif work in this situation?

If I have a 2 in A1, A2, A3 and a 3 in B1 I would want an answer of one, not four...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Lilliabeth]

We posted at the same time, my previous reply was intended for Skip's post.

Please answer the original poster's question using COUNTIF so next time I can post at least a couple of possibilities.
Thanks.

 

[xlhelp]

Wouldn't a simple

=SUM((A:A=2)*(B:B=3)) as an array formula [CTRL+SHIFT+ENTER] suffice?


Member AAA - Abolish Abused Abbreviations

 

[AnotherHiggins]

I would say there is the "row thing" and the "efficiency thing".

How about the "right answer thing"?

Seriously - I have never seen CountIf used for this type of problem. If Lilliabeth and I are missing something, please edify us.

Wouldn't a simple

=SUM((A:A=2)*(B:B=3)) as an array formula [CTRL+SHIFT+ENTER] suffice?

Did you try it?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

Hmm, the "right answer thing" is really escaping me here. CountIf is highly underated and has one of the fastest algorithm's in the existence of the Excel application. We can't match it with convuluted formula (not saying there is any here) or iva VBA. Granted there is some speed loss due to two function calls, but it's not much with the function.

The very bottom line is, both solutions can work, neither one is really more "right" than the other; it boils down to preference and fine tuning. It's like building a custom car, the parts you put into it should compliment each other and be the most efficient as possible (if say, we were all rich).

-----------
Regards,
Zack Barresse

 

[xlhelp]

Yes, John, I did. Could you, please?

Member AAA - Abolish Abused Abbreviations

 

[AnotherHiggins]

firefytr,

I think the issue is as follows. Let's say I have the following data:
[tt]
ColA ColB
2 3
2 3
2 0
2 0
0 3
0 3
0 0
2 3[/tt]

If we want to count how many times ColA = 2 AND ColB = 3 (3 in my example), how could we use CountIf to get the same answer as [COLOR=blue white]=SUMPRODUCT((A1:A65500=2)*(B1:B65500=3))[/color]?

xlhelp,
What version of Excel are you using? I'm on Excel 2000 and it does not work at all for me - I just get #NUM! when using "A:A" and "B:B". It will work, but only if I specify ranges such as =SUM((A1:A65535=2)*(B1:B65535=3)).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlhelp]

My apologies. I tried it with a range I guess. And when I got around to posting it, I changed it to columns.

Member AAA - Abolish Abused Abbreviations