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

  • Paul McGuinness
    Paul McGuinness Overachievers

    Hi @Coen

    This should do the trick

    =SUMIFS([Estimated Refund Value]:[Estimated Refund Value], [Credit Calculator]:[Credit Calculator],TRUE)

    Hope that helps

    Thanks

    Paul

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    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! 🙂

  • Monique Odom-Stearn
    Monique Odom-Stearn ✭✭✭✭✭✭

    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

  • Coen
    Coen ✭✭✭✭
    edited 09/06/23

    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 :)

  • Coen
    Coen ✭✭✭✭
    edited 09/06/23

    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.

  • Monique Odom-Stearn
    Monique Odom-Stearn ✭✭✭✭✭✭

    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

  • Coen
    Coen ✭✭✭✭
    edited 09/06/23

    @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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    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.

  • Coen
    Coen ✭✭✭✭
    edited 09/06/23

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!