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

 

[Excel Hopeful]

John, all data is text.

 

[DominicB]

Hi Excel Hopeful

Following on from Datsmart's thought - try putting the 1 in quotes (""), as it's not in quotes it's expecting a numeric value.

HTH

DominicB

 

[Datsmart]

Your example in your first post shows numeric values in the first column. Is your actual data different? The formula will work with text or numbers, but your comparisons have to match.
D2:D5715 = Text
A8 = Text
-or-
D2:D5715 = Numbers
A8 = Number

 

[Excel Hopeful]

JOhn IT WORKED!!! I only had to add "Sheet1!" to your original forumula
at this spotH2:H5715<>"")


SUMPRODUCT(--(Sheet1!D2:D5715=A4),--(Sheet1!H2:H5715=E4),--(sheet1!H2:H5715<>""))

Thank you both!!!!!!!!!!!1