Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct - change criteria based on input choice
I am using Sumproduct with three variables (Person, Product and Month).
I am using data validation lists for the end user to select the person, product and month. I would like to be able to add a "Total" to the person list so that when the end user chooses "Total" and a product and month that the Sumproduct would only use the two other criteria (Product and Month) and be able to see the totals of those criteria for all Persons. I know I could nest IF THEN statements with the Sumproduct, but is there a better way??? Appreciate the help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct - change criteria based on input choice
No, an IF/THEN method of nesting two different versions of the formulas is
the way to do this. -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Steve" wrote: I am using Sumproduct with three variables (Person, Product and Month). I am using data validation lists for the end user to select the person, product and month. I would like to be able to add a "Total" to the person list so that when the end user chooses "Total" and a product and month that the Sumproduct would only use the two other criteria (Product and Month) and be able to see the totals of those criteria for all Persons. I know I could nest IF THEN statements with the Sumproduct, but is there a better way??? Appreciate the help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct - change criteria based on input choice
Would it work better to use SUMIFS?
"JBeaucaire" wrote: No, an IF/THEN method of nesting two different versions of the formulas is the way to do this. -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Steve" wrote: I am using Sumproduct with three variables (Person, Product and Month). I am using data validation lists for the end user to select the person, product and month. I would like to be able to add a "Total" to the person list so that when the end user chooses "Total" and a product and month that the Sumproduct would only use the two other criteria (Product and Month) and be able to see the totals of those criteria for all Persons. I know I could nest IF THEN statements with the Sumproduct, but is there a better way??? Appreciate the help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct - change criteria based on input choice
I can't think of how. Just because a formula is long doesn't make it
undesirable. An IF/THEN is only going to run one of the two nested SUMPRODUCT() formulas. -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Steve" wrote: Would it work better to use SUMIFS? "JBeaucaire" wrote: No, an IF/THEN method of nesting two different versions of the formulas is the way to do this. -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Steve" wrote: I am using Sumproduct with three variables (Person, Product and Month). I am using data validation lists for the end user to select the person, product and month. I would like to be able to add a "Total" to the person list so that when the end user chooses "Total" and a product and month that the Sumproduct would only use the two other criteria (Product and Month) and be able to see the totals of those criteria for all Persons. I know I could nest IF THEN statements with the Sumproduct, but is there a better way??? Appreciate the help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct - change criteria based on input choice
You can use an array formula** like this:
=SUM((IF(A1="total",ROW(A5:A20)^0,A5:A20=A1))*(B5: B20=B1)*(C5:C20=C1)*D5:D20) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I am using Sumproduct with three variables (Person, Product and Month). I am using data validation lists for the end user to select the person, product and month. I would like to be able to add a "Total" to the person list so that when the end user chooses "Total" and a product and month that the Sumproduct would only use the two other criteria (Product and Month) and be able to see the totals of those criteria for all Persons. I know I could nest IF THEN statements with the Sumproduct, but is there a better way??? Appreciate the help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I make a picture change based on input from a user? | Excel Discussion (Misc queries) | |||
Sumproduct based on certain criteria | Excel Discussion (Misc queries) | |||
IF-based SUMPRODUCT criteria | Excel Discussion (Misc queries) | |||
How do I change the format of a cell based on what I input? | Excel Worksheet Functions | |||
How do you change font color based on the value input? 1 = |
Excel Discussion (Misc queries) |