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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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.
 

Forum statistics

Threads
1,141,139
Messages
5,704,515
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top