# 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?

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)

• ✭✭✭✭✭✭
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

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

• Employee
Options

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

=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

October 8 - 10, Seattle, WA | Register now

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)

• Employee
Options

Awesome! I'm glad to hear it 🙂