combining two formulas for one result

[Kathleen]

Hi,

I have a form that requires the user to rate a response as 1, 3 or 5 by
picking the number from a drop down list. The sum is then added in the
"total" line. If a response has a rating of 1, I want the "total" to be zero
regardless of what the true total is.

A1 = 1, B1=3, C1=5
D1 shows a total of 9
However A1 = 1 so D1 should show a total of 0


Kathleen

 

[Carl Witthoft]

Hi,

I have a form that requires the user to rate a response as 1, 3 or 5 by
picking the number from a drop down list. The sum is then added in the
"total" line. If a response has a rating of 1, I want the "total" to be zero
regardless of what the true total is.

A1 = 1, B1=3, C1=5
D1 shows a total of 9
However A1 = 1 so D1 should show a total of 0


Kathleen

So, you want D1 to be 0 if any of A1, B1, C1 are zero?
It can be done w/ a bunch of fancy IF things, or more simply with

=(A1+B1+C1)*((A1-1)*(B1-1)*(C1-1)>0)

Now, this formula (as well as various IF(IF(IF....))) things) will
get out of hand in a hurry if you actually want to sum up maybe 50 or
100 responses.
Then, do the same thing, but use an array formula:

=sum(a1:z1)*(product(a1:z1-1)>0)

To enter this as an array formula, use Apple-Return (clover-Return).

PS I tried this and so far as I can tell it works just ducky

 

[CyberTaz]

Here's one option:

=IF(OR(A1=1,B1=1,C1=1),0,SUM(A1:C1))

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Kathleen]

THANK YOU THANK YOU THANK YOU!!! Its works beautifully!!

 

[Kathleen]

THANK YOU THANK YOU THANK YOU!!! It works beautifully!!

 

[Carl Witthoft]

Does this mean dinner's on you next time?

Glad I could help.

Carl