# Counting on 2 Conditions

#### jpiazza

##### New Member
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
One solution, where D1 contains, for your example, B:
=SUMPRODUCT((A1:A6=B1:B6)*(A1:A6=D1))

#### jpiazza

##### New Member
Will that Count the number of times "B" is in both columns ? Or try to multiply ?

#### RalphA

##### Well-known Member
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

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
Perhaps you should first read up on the Excel AND() function. Its syntax is:
AND(logical1,logical2,...)
So, your formula should probably be:

#### jpiazza

##### New Member

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
Try something like this:

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

#### jpiazza

##### New Member
That returns a #NUM! error

#### RalphA

##### Well-known Member
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...

Replies
4
Views
214
Replies
0
Views
263
Replies
0
Views
372
Replies
3
Views
422
Replies
5
Views
83

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.

### Which adblocker are you using?

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

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