Is there an equivalent of sumproduct but for count?
 

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

Is there an equivalent of sumproduct but for count?
 

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


"PGarcia" wrote in message
...
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))

Is there an equivalent of sumproduct but for count?
 

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.


"Bernie Deitrick" wrote:

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


"PGarcia" wrote in message
...
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))

Is there an equivalent of sumproduct but for count?
 

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.

--
HTH,
Bernie
MS Excel MVP


"PGarcia" wrote in message
...
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.


"Bernie Deitrick" wrote:

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


"PGarcia" wrote in message
...
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))

Is there an equivalent of sumproduct but for 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

"Bernie Deitrick" wrote:

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.

--
HTH,
Bernie
MS Excel MVP


"PGarcia" wrote in message
...
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.


"Bernie Deitrick" wrote:

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


"PGarcia" wrote in message
...
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))

Is there an equivalent of sumproduct but for count?
 

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


"PGarcia" wrote in message
...
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

"Bernie Deitrick" wrote:

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.

--
HTH,
Bernie
MS Excel MVP


"PGarcia" wrote in message
...
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.


"Bernie Deitrick" wrote:

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


"PGarcia" wrote in message
...
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))

Is there an equivalent of sumproduct but for count?
 

I guess my mind is playing tricks on my. It does work. Thanks

"Bernie Deitrick" wrote:

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


"PGarcia" wrote in message
...
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

"Bernie Deitrick" wrote:

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.

--
HTH,
Bernie
MS Excel MVP


"PGarcia" wrote in message
...
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.


"Bernie Deitrick" wrote:

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


"PGarcia" wrote in message
...
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))