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!

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!

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!

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!

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!