Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT--what else??
I'm auditing a spreadsheet with the following formula:
=SUMPRODUCT((E81:P81)*(E59:P59="BAU"))+SUMPRODUCT( (E81:P81)*(E59:P59="BAU Starts")) I'm looking for confirmation that the following is the correct interpretation of what this formula is doing: 1) Sum those cells in the range E81:P81 for which the corresponding cell in range E59:P59 contains "BAU" 2) Sum those cells in the range E81:P81 for which the corresponding cell in range E59:P59 contains "BAU Starts" 3) Add the results of 1) and 2) together. Is this correct? Thx, Dave -- Brevity is the soul of wit. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT--what else??
Hey, Dave-
I mocked up a sprdsht and found that yes, the formula is doing what you interpret it to be doing- with the exception that the corresponding range e59:p59 ~equals~, not contains, BAU or BAU Starts. (Not to be a nitpicker, just a detail). Dave O |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT--what else??
Is this correct?
Yes. An alternative: =SUM(SUMIF(E59:P59,{"BAU","BAU STARTS"},E81:P81)) Biff "Dave F" wrote in message ... I'm auditing a spreadsheet with the following formula: =SUMPRODUCT((E81:P81)*(E59:P59="BAU"))+SUMPRODUCT( (E81:P81)*(E59:P59="BAU Starts")) I'm looking for confirmation that the following is the correct interpretation of what this formula is doing: 1) Sum those cells in the range E81:P81 for which the corresponding cell in range E59:P59 contains "BAU" 2) Sum those cells in the range E81:P81 for which the corresponding cell in range E59:P59 contains "BAU Starts" 3) Add the results of 1) and 2) together. Is this correct? Thx, Dave -- Brevity is the soul of wit. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT--what else??
Why be slow and obtuse
=SUMIF(E59:P59,"BAU",E81:P81)+SUMIF(E59:P59,"BAU STARTS",E81:P81) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Biff" wrote in message ... Is this correct? Yes. An alternative: =SUM(SUMIF(E59:P59,{"BAU","BAU STARTS"},E81:P81)) Biff "Dave F" wrote in message ... I'm auditing a spreadsheet with the following formula: =SUMPRODUCT((E81:P81)*(E59:P59="BAU"))+SUMPRODUCT( (E81:P81)*(E59:P59="BAU Starts")) I'm looking for confirmation that the following is the correct interpretation of what this formula is doing: 1) Sum those cells in the range E81:P81 for which the corresponding cell in range E59:P59 contains "BAU" 2) Sum those cells in the range E81:P81 for which the corresponding cell in range E59:P59 contains "BAU Starts" 3) Add the results of 1) and 2) together. Is this correct? Thx, Dave -- Brevity is the soul of wit. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT--what else??
Thanks to all.
Interesting alternatives. Dave -- Brevity is the soul of wit. "Bob Phillips" wrote: Why be slow and obtuse =SUMIF(E59:P59,"BAU",E81:P81)+SUMIF(E59:P59,"BAU STARTS",E81:P81) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Biff" wrote in message ... Is this correct? Yes. An alternative: =SUM(SUMIF(E59:P59,{"BAU","BAU STARTS"},E81:P81)) Biff "Dave F" wrote in message ... I'm auditing a spreadsheet with the following formula: =SUMPRODUCT((E81:P81)*(E59:P59="BAU"))+SUMPRODUCT( (E81:P81)*(E59:P59="BAU Starts")) I'm looking for confirmation that the following is the correct interpretation of what this formula is doing: 1) Sum those cells in the range E81:P81 for which the corresponding cell in range E59:P59 contains "BAU" 2) Sum those cells in the range E81:P81 for which the corresponding cell in range E59:P59 contains "BAU Starts" 3) Add the results of 1) and 2) together. Is this correct? Thx, Dave -- Brevity is the soul of wit. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |