Two formulas

Carlos Yanes

Hey all - hoping you can help me with the formula below. Trying to combine two of them but keep getting an error.

=(COUNT({Ready for Migration Range 4}) - (=SUM([Column5]27:[Column5]32)))

In the first section I'm doing a count, second one if summing a set of numbers and then subtracting the two values. Any guidance or help would be greatly appreciated. :)

Mike Wilday

No worries! :)

It's good to know that I'm not the only human helping out in the community! 😄

L_123

=COUNT({Ready for Migration Range 4}) - SUM([Column5]27:[Column5]32)

Mike Wilday

To elaborate on l@123's answer, you had too many parenthesis and the wrong number of closing parenthesis. Smartsheet is REALLY particular with parenthesis and matching parenthesis.

L_123

Honestly even with the parenthesis used, while excessive, wouldn't have stopped this from working as they all match up. The equals sign before the sum is what stopped it, though I should have said that. My bad.

Mike Wilday

haha. yes that too. :) Good thing you spoke up! I was just trying to provide clarity but i clearly didn't look long enough.

Carlos Yanes

Thank you for the clarification. The formula didn't generate an error now, but it's not calculating correctly. It's resulting in a "1" when it should be a "5".

L_123

Can you share a snip of the values in the columns? You can black out any sensitive information.

Carlos Yanes

Sure, see below -

L_123

And what ranges are your references supposed to check? One is a different sheet reference, and the other is column5. I don't see either on your picture.

Did you mean:

=COUNT({Ready for Migration Range 4}) - SUM([Total]27:[Total]32)

Carlos Yanes

I think that's what i meant (or want to happen) but it gives me an error.

L_123

Try each part of the formula individually. Which one gives an error?

=COUNT({Ready for Migration Range 4})

=SUM([Total]27:[Total]32)

Carlos Yanes

The formula works when broken up, but not when it's combined. Any ideas why?

L_123

What numbers are they producing? The sum should be 285 from your snip, what are you getting returned on the count?

Paul Newcome

Which error are you getting?

Carlos Yanes

If I use this formula - =COUNT({Ready for Migration Range 4}) - SUM([Total]27:[Total]32) I get the unparseable error.

L_123

Have you double checked the range {Ready for Migration Range 4}?

Place your cursor on this range then select "Edit Reference". Make sure that it is the correct range. Sometimes smartsheet will delete these ranges on creation.