Can we use a Sum or Avg formula to rows/columns which has a IF condition.

Keith Clarkson

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.

Josh Reed

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?

Josh Reed

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?

Keith Clarkson

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 Drop-down 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.

Keith Clarkson

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

Josh Reed

@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 # 😁