# 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

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

#### 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
1
Views
50
Replies
3
Views
48
Replies
3
Views
34
Replies
7
Views
39
Replies
3
Views
28