Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with SUMPRODUCT
I'd like to calcluate a range based on 3 different conditions:
Area="Northeast" Closed Month="OCT","NOV","DEC" Sales Stage="02 - Qualified" Range to calculate: Total Sales (times 25%) The formula I used is this: =SUMPRODUCT((A13:A68="US-Northeast")*(I13:I68,{"OCT","NOV","DEC"})*(K13:K68 ="02 - Qualified")*((F13:F68)*.25)) Yet it's giving me an error. The thing that is raising my eyebrow on it is, when the formula is getting written, you can see the ranges selected in the color-coded boxes, but only two ranges seem to be selected: "Area" and "Close Month". "Sales Stage" and "Total Revenue" are not selected for some reason. Any ideas? |
#2
|
|||
|
|||
Try
=SUMPRODUCT((A13:A68="US-Northeast")*(I13:I68="OCT","NOV","DEC"})*(K13:K68= " 02 - Qualified"),F13:F68))*.25 -- HTH Bob Phillips "FrankTimJr" wrote in message ... I'd like to calcluate a range based on 3 different conditions: Area="Northeast" Closed Month="OCT","NOV","DEC" Sales Stage="02 - Qualified" Range to calculate: Total Sales (times 25%) The formula I used is this: =SUMPRODUCT((A13:A68="US-Northeast")*(I13:I68,{"OCT","NOV","DEC"})*(K13:K68 = "02 - Qualified")*((F13:F68)*.25)) Yet it's giving me an error. The thing that is raising my eyebrow on it is, when the formula is getting written, you can see the ranges selected in the color-coded boxes, but only two ranges seem to be selected: "Area" and "Close Month". "Sales Stage" and "Total Revenue" are not selected for some reason. Any ideas? |
#3
|
|||
|
|||
Try...
=SUMPRODUCT(--(A13:A68="US-Northeast"),--(ISNUMBER(MATCH(I13:I68,{"OCT"," NOV","DEC"},0))),--(K13:K68="02 - Qualified"),F13:F68*0.25) Hope this helps! In article , "FrankTimJr" wrote: I'd like to calcluate a range based on 3 different conditions: Area="Northeast" Closed Month="OCT","NOV","DEC" Sales Stage="02 - Qualified" Range to calculate: Total Sales (times 25%) The formula I used is this: =SUMPRODUCT((A13:A68="US-Northeast")*(I13:I68,{"OCT","NOV","DEC"})*(K13:K68 ="0 2 - Qualified")*((F13:F68)*.25)) Yet it's giving me an error. The thing that is raising my eyebrow on it is, when the formula is getting written, you can see the ranges selected in the color-coded boxes, but only two ranges seem to be selected: "Area" and "Close Month". "Sales Stage" and "Total Revenue" are not selected for some reason. Any ideas? |
#4
|
|||
|
|||
No good. I had to modifiy what you typed just a tad..in
....(I13:I68="OCT,"NOV","DEC}, shouldn't it be (I13:I68,{"OCT,"NOV","DEC})...? Now it looks like this: SUMPRODUCT((A13:A68="US-Northeast")*(I13:I68,{"OCT","NOV","DEC"})*(K13:K68 ="02 - Qualified"),(F13:F68))*0.25 But still no dice. Is the SUMPRODUCT limited to only 2 conditions, then suming the values? I can get it to work easily if I had only the two conditions, but now that I have three, that's when it stopped working. "Bob Phillips" wrote: Try =SUMPRODUCT((A13:A68="US-Northeast")*(I13:I68="OCT","NOV","DEC"})*(K13:K68= " 02 - Qualified"),F13:F68))*.25 -- HTH Bob Phillips "FrankTimJr" wrote in message ... I'd like to calcluate a range based on 3 different conditions: Area="Northeast" Closed Month="OCT","NOV","DEC" Sales Stage="02 - Qualified" Range to calculate: Total Sales (times 25%) The formula I used is this: =SUMPRODUCT((A13:A68="US-Northeast")*(I13:I68,{"OCT","NOV","DEC"})*(K13:K68 = "02 - Qualified")*((F13:F68)*.25)) Yet it's giving me an error. The thing that is raising my eyebrow on it is, when the formula is getting written, you can see the ranges selected in the color-coded boxes, but only two ranges seem to be selected: "Area" and "Close Month". "Sales Stage" and "Total Revenue" are not selected for some reason. Any ideas? |
#5
|
|||
|
|||
That worked perfectly. I wish I could understand it however.
"Domenic" wrote: Try... =SUMPRODUCT(--(A13:A68="US-Northeast"),--(ISNUMBER(MATCH(I13:I68,{"OCT"," NOV","DEC"},0))),--(K13:K68="02 - Qualified"),F13:F68*0.25) Hope this helps! In article , "FrankTimJr" wrote: I'd like to calcluate a range based on 3 different conditions: Area="Northeast" Closed Month="OCT","NOV","DEC" Sales Stage="02 - Qualified" Range to calculate: Total Sales (times 25%) The formula I used is this: =SUMPRODUCT((A13:A68="US-Northeast")*(I13:I68,{"OCT","NOV","DEC"})*(K13:K68 ="0 2 - Qualified")*((F13:F68)*.25)) Yet it's giving me an error. The thing that is raising my eyebrow on it is, when the formula is getting written, you can see the ranges selected in the color-coded boxes, but only two ranges seem to be selected: "Area" and "Close Month". "Sales Stage" and "Total Revenue" are not selected for some reason. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |