Sum Collect with Checkbox

RachalRobles

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?

RachalRobles

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)

Dale Murphy

@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

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.

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

RachalRobles

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.

Awesome! I'm glad to hear it 🙂