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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
One solution, where D1 contains, for your example, B:
=SUMPRODUCT((A1:A6=B1:B6)*(A1:A6=D1))
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
Try something like this:

=SUMPRODUCT((condition 1)*(condition2))
Say condition1 is ('Active Tickets'!L:L="Request").
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top