Is there an equivalent of sumproduct but for count?

  • Thread starter Thread starter PGarcia
  • Start date Start date
P

PGarcia

I have the following formla, but am look to do the same with "count". Thanks

=SUMPRODUCT(--('All Divisions'!A2:A3039="Eastern"),--('All
Divisions'!J2:J3039>=30))
 
That should return the COUNT of items "Eatern" and >=30. Do you mean SUM, perhaps?

=SUMPRODUCT(('All Divisions'!A2:A3039="Eastern")*
('All Divisions'!J2:J3039>=30)*('All Divisions'!J2:J3039))

HTH,
Bernie
MS Excel MVP
 
Thanks, but that gave me 41964 and it should have returned 395.

I was looking to get how many “Eastern†that are over 30 days old there are,
e.g. 3 or 25 day over 30. The formula provided adds up the days that are
there, e.g. 31 days, 45 days, 76 days, 103 days = 255

Tnanks.
 
My point was that the formula that you posted:
=SUMPRODUCT(--('All Divisions'!A2:A3039="Eastern"),--('All Divisions'!J2:J3039>=30))
should return your desired COUNT.
 
I give me the sum of days >30. Here is the data:
Division Days Old
Eastern 59
Eastern 3
Eastern 3
Eastern 108
Eastern 105
Eastern 13
Eastern 13
Eastern 7
Eastern 7
Eastern 9
Eastern 10
Eastern 10
Eastern 90
Eastern 148

So, the formlua is giveing me the sum = 677, but I want the count =5
 
Wtih your data (Eastern in column A, numbers in column J), the formula

=SUMPRODUCT(--('All Divisions'!A2:A3039="Eastern"),--('All Divisions'!J2:J3039>=30))

returns 5.

This part

--('All Divisions'!A2:A3039="Eastern")

returns an array of 1s and 0s -- 1 when the cell in column A is "Eastern", and 0 when it isn't.

The other part returns an array of 1s and 0s, --- 1 when the value is >=30.

Mutliplied together, they give the count.


You may have some data that you don't see waaaay down the sheet. Try this shorter formula with your
example data set.

=SUMPRODUCT(--('All Divisions'!A2:A15="Eastern"),--('All Divisions'!J2:J15>=30))

That should definitely return 5, while

=SUMPRODUCT(--('All Divisions'!A2:A15="Eastern"),--('All Divisions'!J2:J15>=30),('All
Divisions'!J2:J15))

should return 510.


HTH,
Bernie
MS Excel MVP
 
Back
Top