Count based on two criteria from 2 columns

[Excel Hopeful]

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

 

[Datsmart]

=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]

Thanks. I tried the formula but it comes out zero.

What do the - - represent?

 

[Datsmart]

This posting explains the use of the double negatives:
http://www.mrexcel.com/board2/viewtopic.php?t=133602

 

[Excel Hopeful]

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]

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]

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]

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]

Hi DominicB,

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

 

[Datsmart]

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.