Counting on 2 Conditions

jpiazza

New Member
Joined
Oct 10, 2006
Messages
15
I am trying to count on 2 conditions being true in 2 differant columns.
For example

Column A | Column B
A B
A B
B B
B C

I want to count IF column A=B AND column B=B
Is DCount the right answer ?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
One solution, where D1 contains, for your example, B:
=SUMPRODUCT((A1:A6=B1:B6)*(A1:A6=D1))
 

jpiazza

New Member
Joined
Oct 10, 2006
Messages
15
Will that Count the number of times "B" is in both columns ? Or try to multiply ?
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
The formula =SUMPRODUCT((A1:A6=B1:B6)*(A1:A6=D1))
will return, for the first parenthesis (A1:A6=B1:B6), a one if the if a cell in column A and a cell in column B that are on the same row are equal, whatever the equal data is (could be A and A, or Abc and Abc, or 123 and 123, etc.); likewise, the second parenthesis, (A1:A6=D1), will return a 1 if any of the cells in the range A1:A6 equal the data in cell D1, otherwise, a 0; then the SUMPRODUCT() function adds the number of rows that have a 1 as the result of both parenthesis, thereby counting the number of times a cell in column A is both equal to a cell in column B on the same row, and also equal to the data in cell D1.

I believe that is what you asked for. Why don't you run a few sample cases, and convince yourself if my formula works as you want or not, instead of asking more questions?
 

jpiazza

New Member
Joined
Oct 10, 2006
Messages
15

ADVERTISEMENT

Thanks, I've done that and hence why I am asking follow-up questions. I may not have been clear in my initial request, so let me clarify.

Basically what I am trying to do is this

=COUNTIF('Active Tickets'!L:L,"Request") AND COUNTIF('Active Tickets'!H:H,"P3")

So the values are not equal, it is counting if both of these conditions are true.

But the above formula does not work and Excel complains about it.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Perhaps you should first read up on the Excel AND() function. Its syntax is:
AND(logical1,logical2,...)
So, your formula should probably be:

=AND(COUNTIF(your stuff1 here),COUNTIF(your stuff2 here))
 

jpiazza

New Member
Joined
Oct 10, 2006
Messages
15

ADVERTISEMENT

Thanks, I had already tried that, and just tried again. That formula simpley returns a TRUE or FALSE. It appears that this isn't that easy of a problem to solve.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Try something like this:

=SUMPRODUCT((condition 1)*(condition2))
Say condition1 is ('Active Tickets'!L:L="Request").
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
I'm not into using only column names, such as your L:L. Perhaps, if you use a range, such as L3:L55, you will get proper results? It seems to work for me...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,787
Messages
5,524,875
Members
409,608
Latest member
GigaPat

This Week's Hot Topics

Top