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 ?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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...
 

Forum statistics

Threads
1,141,218
Messages
5,705,076
Members
421,377
Latest member
FerdiFuchs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top