Count based on two criteria from 2 columns

Excel Hopeful

Board Regular
Joined
Mar 13, 2006
Messages
100
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)))
 

Some videos you may like

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
Joined
Jun 19, 2003
Messages
7,985
=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
Joined
Mar 13, 2006
Messages
100

ADVERTISEMENT

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
Joined
Oct 3, 2005
Messages
1,569
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
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

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
Joined
Mar 13, 2006
Messages
100
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
Joined
Mar 13, 2006
Messages
100
Hi DominicB,

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

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,362
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top