Sum Collect with Checkbox
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
-
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
-
@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
-
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)))
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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)
-
Awesome! I'm glad to hear it 🙂
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 150 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!