| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with OR condition
There is no need for double multiplying C2:C10.
You can use a shorter formula: =SUMPRODUCT(((A2:A10="Bob")+(A2:A10<"Bob")*(B2:B1 0="Feb-10"))*C2:C10) Micky "Michael_R" wrote: I have the following table: Name Month Sales Mike Feb-10 1 Bob Mar-10 2 Alex Mar-10 4 Bob Mar-10 8 Bob Feb-10 16 In order to get all sales of Bob plus all sales (by anybody else) in Feb-10 (result = 27), I devised the following formula: =SUMIF(Name,"Bob",Sales)+SUMPRODUCT((Name<"Bob")* (Month="Feb-10"),Sales) Question: Is there a (simple) way to produce the required result using only one SUMPRODUCT ie getting rid of the SUMIF? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT with OR condition
Micky, this is an amazing formula. Thanks!
"מיכאל (מיקי) אבידן" wrote: There is no need for double multiplying C2:C10. You can use a shorter formula: =SUMPRODUCT(((A2:A10="Bob")+(A2:A10<"Bob")*(B2:B1 0="Feb-10"))*C2:C10) Micky "Michael_R" wrote: I have the following table: Name Month Sales Mike Feb-10 1 Bob Mar-10 2 Alex Mar-10 4 Bob Mar-10 8 Bob Feb-10 16 In order to get all sales of Bob plus all sales (by anybody else) in Feb-10 (result = 27), I devised the following formula: =SUMIF(Name,"Bob",Sales)+SUMPRODUCT((Name<"Bob")* (Month="Feb-10"),Sales) Question: Is there a (simple) way to produce the required result using only one SUMPRODUCT ie getting rid of the SUMIF? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| Sumproduct 'Or' Condition | Excel Worksheet Functions | |||
| SUMPRODUCT with an IF condition? | Excel Discussion (Misc queries) | |||
| Sumproduct: condition with ? | Excel Discussion (Misc queries) | |||
| Sumproduct with condition??? | Excel Discussion (Misc queries) | |||
Hybrid Mode
