Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct of columns
Hello~
For each row in my report I need to add columns ES:EZ and determine if the val is equal to zero, then return a count of all rows where columns ES:EZ added up to 0 . Can I use Sumproduct for this or is there another way? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct of columns
DJS
How about this Add a helper column in Column FA with the following fomula and copy down to the end of your data =SUM(ES2:EZ2) Use this formula to count the zeros. =COUNTIF(FA2:FA200,"=0") Adjust ranges to meet your data requirements. Dave "DJS" wrote in message ... Hello~ For each row in my report I need to add columns ES:EZ and determine if the val is equal to zero, then return a count of all rows where columns ES:EZ added up to 0 . Can I use Sumproduct for this or is there another way? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct of columns
Thanks Dave, but i can't modify that sheet.
All of my functions are in my main sheet and reference other sheets or tabs. Any other way to do this from one cell in my main sheet? "Dave" wrote: DJS How about this Add a helper column in Column FA with the following fomula and copy down to the end of your data =SUM(ES2:EZ2) Use this formula to count the zeros. =COUNTIF(FA2:FA200,"=0") Adjust ranges to meet your data requirements. Dave "DJS" wrote in message ... Hello~ For each row in my report I need to add columns ES:EZ and determine if the val is equal to zero, then return a count of all rows where columns ES:EZ added up to 0 . Can I use Sumproduct for this or is there another way? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct of columns
DJS
Try this =SUMPRODUCT(--((ES2:ES5)=0),--((ET2:ET5)=0),--((EU2:EU5)=0),--((EV2:EV5)=0),--((EW2:EW5)=0),--((EX2:EX5)=0),--((EY2:EY5)=0),--((EZ2:EZ5)=0)) Adjust ranges to suit. Note all ranges must be the same size. Dave "DJS" wrote in message ... Thanks Dave, but i can't modify that sheet. All of my functions are in my main sheet and reference other sheets or tabs. Any other way to do this from one cell in my main sheet? "Dave" wrote: DJS How about this Add a helper column in Column FA with the following fomula and copy down to the end of your data =SUM(ES2:EZ2) Use this formula to count the zeros. =COUNTIF(FA2:FA200,"=0") Adjust ranges to meet your data requirements. Dave "DJS" wrote in message ... Hello~ For each row in my report I need to add columns ES:EZ and determine if the val is equal to zero, then return a count of all rows where columns ES:EZ added up to 0 . Can I use Sumproduct for this or is there another way? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct of columns
Thanks Dave, that's the ticket!
I thought I tried that simple function, but didn't seem to get the same results. "Dave" wrote: DJS Try this =SUMPRODUCT(--((ES2:ES5)=0),--((ET2:ET5)=0),--((EU2:EU5)=0),--((EV2:EV5)=0),--((EW2:EW5)=0),--((EX2:EX5)=0),--((EY2:EY5)=0),--((EZ2:EZ5)=0)) Adjust ranges to suit. Note all ranges must be the same size. Dave "DJS" wrote in message ... Thanks Dave, but i can't modify that sheet. All of my functions are in my main sheet and reference other sheets or tabs. Any other way to do this from one cell in my main sheet? "Dave" wrote: DJS How about this Add a helper column in Column FA with the following fomula and copy down to the end of your data =SUM(ES2:EZ2) Use this formula to count the zeros. =COUNTIF(FA2:FA200,"=0") Adjust ranges to meet your data requirements. Dave "DJS" wrote in message ... Hello~ For each row in my report I need to add columns ES:EZ and determine if the val is equal to zero, then return a count of all rows where columns ES:EZ added up to 0 . Can I use Sumproduct for this or is there another way? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct of columns
Glad to help.
"DJS" wrote in message ... Thanks Dave, that's the ticket! I thought I tried that simple function, but didn't seem to get the same results. "Dave" wrote: DJS Try this =SUMPRODUCT(--((ES2:ES5)=0),--((ET2:ET5)=0),--((EU2:EU5)=0),--((EV2:EV5)=0),--((EW2:EW5)=0),--((EX2:EX5)=0),--((EY2:EY5)=0),--((EZ2:EZ5)=0)) Adjust ranges to suit. Note all ranges must be the same size. Dave "DJS" wrote in message ... Thanks Dave, but i can't modify that sheet. All of my functions are in my main sheet and reference other sheets or tabs. Any other way to do this from one cell in my main sheet? "Dave" wrote: DJS How about this Add a helper column in Column FA with the following fomula and copy down to the end of your data =SUM(ES2:EZ2) Use this formula to count the zeros. =COUNTIF(FA2:FA200,"=0") Adjust ranges to meet your data requirements. Dave "DJS" wrote in message ... Hello~ For each row in my report I need to add columns ES:EZ and determine if the val is equal to zero, then return a count of all rows where columns ES:EZ added up to 0 . Can I use Sumproduct for this or is there another way? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |