| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT, count & sum | Excel Worksheet Functions | |||
| count if or sumproduct? | Excel Discussion (Misc queries) | |||
| Excel equivalent of sql group by count | Excel Worksheet Functions | |||
| WORKDAY() Function Equivalent with SUMPRODUCT() | Excel Worksheet Functions | |||
| Count if and Sumproduct | Excel Discussion (Misc queries) | |||
Linear Mode
