IF Checkbox flagged, SUM help requested
I am trying multiple variations on a Sum/IF formula and can't seem to crack it!
My goal is to SUM the rows in the [Estimated Refund Value] Column IF the [Credit Calculator] box is checked.
I've tried this in various forms including using the SUMIF feature as well as my current iteration below
=IF([Credit Calculator]:[Credit Calculator] = 1, SUM([Estimated Refund Value]:[Estimated Refund Value]), "")
not sure what I am missing, but I feel it's something small.
Answers
-
Hi @Coen
This should do the trick
=SUMIFS([Estimated Refund Value]:[Estimated Refund Value], [Credit Calculator]:[Credit Calculator],TRUE)
Hope that helps
Thanks
Paul
-
Hi @Coen,
This should do what you're after:
=SUMIF([Credit calculator]:[Credit calculator], true, [Estimated Refund]:[Estimated Refund])
Hope this helps; if you've any problems/questions then just post! 🙂
-
Hello @Coen,
Try this:
=SUMIFS([Estimated Refund Value]:[Estimated Refund Value], [Credit Calculator]:[Credit Calculator], 1)
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Business Process Excellence Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
-
Thank you, both!
Unfortunately I am still getting errors, this is leading me to think maybe it's not the formula but something else I've set up?
@Paul McGuinness : =SUMIFS([Estimated Refund Value]:[Estimated Refund Value], [Credit Calculator]:[Credit Calculator], true) - I get an Invalid Operation error. :(
@Nick Korna I also get an invalid operation error with your suggestion of =SUMIF([Credit Calculator]:[Credit Calculator], true, [Estimated Refund Value]:[Estimated Refund Value])
BUT - If I change my column I want to SUM to a column without a formula, this works.
=SUMIF([Credit Calculator]:[Credit Calculator], true, Cost:Cost) - This works
Could it be possible that I am getting errors because I have a column formula set up in the [Estimated Refund Value] Column?
I really appreciate the effort and suggestions :)
-
Thank you @Monique_Odom_Comcast I get an invalid operation error
BUT!
if I use a column that doesn's have a formula in it; it works.
=SUMIFS(Cost:Cost, [Credit Calculator]:[Credit Calculator], 1)
Cost is the column that I am using to calculate the [Estimated Refund Value] Column.
-
Hmmm... I don't think a formula will cause an issue, as long as the result is a number. The SUM functions only work on numbers.
@Coen, if you appropriately have numbers (manual or calculated by formula), can you send screenshots of your sheet (with any confidential information removed or blacked out)?
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Business Process Excellence Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
-
@Monique_Odom_Comcast Images below :)
Estimated Refund Value formula is =Cost@row * 0.85
The formula, using Cost and not [Estimated Refund Value] works.
If I add a calculation to the end of the formula I get positive results
=SUMIFS(Cost:Cost, [Credit Calculator]:[Credit Calculator], 1) * 0.85
-
Do you have any errors in your Estimated Refund Value column? If there are any errors in there, these will stop the SUMIF from working properly.
-
@Nick Korna I did!
I've removed the error and am going to re-test the suggestions below! thank you!!
@Nick Korna @Monique_Odom_Comcast @Paul McGuinness
I had one row in my Estimated Refund Value column that had an error in it. I removed the error and all your suggestions worked! Thank you all!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!