# Count based on two criteria from 2 columns

#### Excel Hopeful

##### Board Regular
I have sheet with data. I want to count from 2 sets of criteria. Here is the example of the data:

1 a
1 z
1 a
1 z
2 a
2 z
2 z
3 a
3 z
3 z

I want a formula that counds how many A's the 1's have. It should come out as 2. Here are the formulas i've tried:

'=SUMPRODUCT(Sheet1!D2:D5715=A4,Sheet1!H:H=E4)

=count(if(sheet1!d:d=a4,If(sheet1!h:h=e5)))

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Datsmart

##### Well-known Member
=SUMPRODUCT(--(Sheet1!D2:D5715=A4),--(Sheet1!H2:H5715=E4),--(H2:H5715<>""))

Note: both ranges D2:D5715 and H2:H5715 must have the same number of rows to compare.

#### Excel Hopeful

##### Board Regular
Thanks. I tried the formula but it comes out zero.

What do the - - represent?

#### Excel Hopeful

##### Board Regular

The formula still comes out to zero:
=SUMPRODUCT(--(Sheet1!D2:D5715=A8),--(Sheet1!H2:H5715=E4),--(H2:H5715<>""))

When I check the function it seems to be referencing the correct cells, but the result is not correct?

#### DominicB

##### Well-known Member
Good evening Excel Hopeful

Try this one :

=SUMPRODUCT(--(Sheet1!\$D\$1:\$D\$5715=1),--(Sheet1!\$H\$2:\$H\$5715="z"))

The double minus is referred to as a unary minus - see here for an explanation :

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

My add-in, available via the link below has a function that will build this type of multiple condition function for you.

HTH

DominicB

#### Datsmart

##### Well-known Member

Does the value in cell A8 exist in Sheets("Sheet1").Range("D2:D5715")? Does the value in cell E4 exist in Sheets("Sheet1").Range("H2:H5715")?
Do they both exist in the same row?
All three serarios must prove true.

#### Excel Hopeful

##### Board Regular
Yes, the value in A8 exists multiple times in range D2:D5715 and the value in E4 exists multiple times range H2:H5715. Both ranges are in sheet1, the formula is in sheet2.

Yes, they are in the same row. I've double checked and all 3 senarios are true.

I'm trying to get the same result as when you filter on 2 columns and get the count at the bottom of excel.

#### Excel Hopeful

##### Board Regular
Hi DominicB,

I tried your formula and I get #value, the data couted is all text, does that matter?

#### Datsmart

##### Well-known Member
Is the value in A8 Text or numeric? It should match the values in your D2:D5715 range.

Dominic's formula has a hard coded number 1 in it. If your range is Text, you will get an error.

Replies
8
Views
116
Replies
1
Views
86
Replies
3
Views
90
Replies
3
Views
26
Replies
13
Views
250