Can we use a Sum or Avg formula to rows/columns which has a IF condition.
I was trying to prepare a report in which I used a IF conditions to get the expected value based on my clients response from the forms. Now, I want to do a SUM or AVG with the rows that I have the conditions. I get #DIVIDE BY ZERO ERROR when AVG is applied & I get the final value as 0 when I apply the Sum formula.
Best Answer

Hey @Keith Clarkson
I might have some ideas but I probably need more context. Can you tell me what the IF formulas are returning as the value in the cells you are trying to sum or average?
Assuming these are numbers, smartsheet is likely not recognizing them as numbers. If your IF statement ends in things in quotes like IF(xxx@row = "yes", "100", "0") Smartsheet may not recognize the 100 as a number. In this case try wrapping the TRUE part of the IF statement in a VALUE(100) function to ensure the result is a number.
Alternatively if your IF formula is returning words or phrases, you probably want to use the COUNTIF formula to count how many times specific selections are made.
Does this help?
Answers

Hey @Keith Clarkson
I might have some ideas but I probably need more context. Can you tell me what the IF formulas are returning as the value in the cells you are trying to sum or average?
Assuming these are numbers, smartsheet is likely not recognizing them as numbers. If your IF statement ends in things in quotes like IF(xxx@row = "yes", "100", "0") Smartsheet may not recognize the 100 as a number. In this case try wrapping the TRUE part of the IF statement in a VALUE(100) function to ensure the result is a number.
Alternatively if your IF formula is returning words or phrases, you probably want to use the COUNTIF formula to count how many times specific selections are made.
Does this help?

Thanks for responding back, Josh!
Okay, Let me be more specific on what I am actually doing, I created a Survey Form in which my client can send us feedback on the service we have provided, so this form has answers in a Dropdown list like for example, Very Satisfied, Satisfied, Neutral, Not Satisfied, etc. Now when I receive them, I will convert this response into Scores (Value), For example, Very Satisfied  95, Satisfied  85, Neutral  65, etc.
I have applied the formula =IF(Responsiveness@row = "Very Satisfied", "95", IF(Responsiveness@row = "Satisfied", "85", IF(Responsiveness@row = "Neutral", "75", IF(Responsiveness@row = "Unsatisfied", "65", IF(Responsiveness@row = "Very Unsatisfied", "55"))))).
This gives me different for 5 different questions, like for example, 95,65, 55,85. Now I need to sum up these or get an average for these numbers. So I tried using the formula =SUM([Question 1  Score]@row:[Question 4  Score]@row) and I get the final value as 0. When I try =AVG([Question 1  Score]@row:[Question 4  Score]@row), I get the final value as #DIVIDE BY ZERO.

I just tried adding Value before the IF formula, it worked, Thank You!

@Keith Clarkson So glad it worked! It can be frustrating when you know the syntax of your formula is correct yet the result starts with a # 😁
Help Article Resources
Categories
Check out the Formula Handbook template!