Sum Collect with Checkbox

Options

Hello,

I currently have an equation returning data from a refererence sheet. This equation is working, however, I need to recognize two different checkboxes from the reference sheet and only sum up and return data if one or two of the boxes is checked. Currently its returning all data. Here is the equation that I am using but I can't figure out out to expand it:

=SUM(COLLECT({NAM TrialTrack Master "Forecast"}, {NAM TrialTrack Master Project No}, [Project Name]@row)) + SUM(COLLECT({NAM TrialTrack Master "Updated Forecast"}, {NAM TrialTrack Master "Updated Trial Funding"}, [Project Name]@row))

Again, only want to Sum Collect rows that I have a box checked in the reference sheet. Can anyone advise?

Best Answer

  • RachalRobles
    RachalRobles ✭✭
    Answer ✓
    Options

    Genevieve - That did the trick! I was able to sum data from two different references with respect to two different checkbox columns within the reference sheets. Adding the full function for others to reference if needed. Thank you for the assistance.

    =SUMIFS({NAM TrialTrack Master "Forecast"}, {NAM TrialTrack Master "Current FY"}, 1, {NAM TrialTrack Master Project No}, [Project Name]@row) + SUMIFS({NAM TrialTrack Master "Updated Forecast"}, {NAM TrialTrack Master Sheet Range 1}, 1, {NAM TrialTrack Master "Updated Trial Funding"}, [Project Name]@row) + SUMIFS({Payments - Not Recorded On TT "Inv Forecast"}, {Payments - Not Recorded On TrialTrack "Current FY"}, 1, {Payments - Not Recorded On TrialTrack "Proj No"}, [Project Name]@row)

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Options

    @RachalRobles Have you tried putting an IF statement around your existing equation? along the lines of

    =IF({checkbox is checked}, SUM equation, 0)

    (maybe an OR to account for both checkboxes?)

    dm

  • RachalRobles
    RachalRobles ✭✭
    edited 03/28/23
    Options

    I'm getting a #Unparseable error when I added both of the IF functions around the sum collects:

    =IF({NAM TrialTrack Master Current FY Checked} is checked, SUM(COLLECT({NAM TrialTrack Master "Forecast"}, {NAM TrialTrack Master Project No}, [Project Name]@row))) + IF({NAM TrialTrack Master "Current FY Change"} is checked, SUM(COLLECT({NAM TrialTrack Master "Updated Forecast"}, {NAM TrialTrack Master "Updated Trial Funding"}, [Project Name]@row)))

  • Genevieve P.
    Options

    Hi @RachalRobles

    The "is checked" value in a formula is represented with a 1.

    I would actually suggest using a SUMIFS function with the IF built in! Try something like this:

    =SUMIFS({Column to SUM}, {Checkbox Column}, 1, {Other Column}, "Criteria")

    Or in your case:

    =SUMIFS({NAM TrialTrack Master "Forecast"}, {NAM TrialTrack Master Current FY Checked}, 1, {NAM TrialTrack Master Project No}, [Project Name]@row)

    Does that make sense?

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • RachalRobles
    RachalRobles ✭✭
    Answer ✓
    Options

    Genevieve - That did the trick! I was able to sum data from two different references with respect to two different checkbox columns within the reference sheets. Adding the full function for others to reference if needed. Thank you for the assistance.

    =SUMIFS({NAM TrialTrack Master "Forecast"}, {NAM TrialTrack Master "Current FY"}, 1, {NAM TrialTrack Master Project No}, [Project Name]@row) + SUMIFS({NAM TrialTrack Master "Updated Forecast"}, {NAM TrialTrack Master Sheet Range 1}, 1, {NAM TrialTrack Master "Updated Trial Funding"}, [Project Name]@row) + SUMIFS({Payments - Not Recorded On TT "Inv Forecast"}, {Payments - Not Recorded On TrialTrack "Current FY"}, 1, {Payments - Not Recorded On TrialTrack "Proj No"}, [Project Name]@row)

  • Genevieve P.
    Options

    Awesome! I'm glad to hear it 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!