# 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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
=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.

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

What do the - - represent?

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?

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

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.

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.

Hi DominicB,

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

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
3
Views
155
Replies
2
Views
315
Replies
4
Views
320
Replies
0
Views
175
Replies
2
Views
288

1,216,515
Messages
6,131,111
Members
449,621
Latest member
feaugcruz

### 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.

### Which adblocker are you using?

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

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