Formula to SUM from Multiple Columns with IF Statement
I am developing a Customer Summary roll-up sheet that pulls data from individual Customer sheets. The objective of this roll-up sheet is to pull all of the financials of each customer into a company revenue summary sheet.
The challenge is the dollar values for each project (row) will change as a project moves through the workflow. When a project is awarded, we assign a generic BUDGET. Once scope is developed, we prepare a QUOTE for the Customer. Upon their approval, and through the execution of the project, the ORDER is updated with actual costs, etc.
GOAL - Update the revenue dollars in the roll-up sheet based on a set of criteria. Below is a snapshot of a sample set of data points relevant to this exercise. The yellow highlighted cells are the numbers to be added.
CRITERIA -
- $ are "bucketed" to the Customer Roll-Up 1 sheet into the Month they are projected to invoice ("INVOICING Projected Date")
- If "Quote Approved" is blank, sum BUDGET
- If "Quote is Approved", but "Order Updated" is NOT checked, sum QUOTE Total
- If "Order Updated" is checked, sum ORDER Total
Source Sheet:
Roll-Up (result should be)
Any ideas on how this formula would be developed?
Customer 1 Range 1 = Budget
Customer 1 Range 2 = Quote Total
Customer 1 Range 3 - Order Total
Customer 1 Range 4 = INVOICING Projected Date
=SUMIFS( _________, {Customer 1 Range 4}, >=$[Month Beginning]@row, {Customer 1 Range 4}, <=$[Month Ending]@row)
Thank you so much in advance!
Kate
Answers
-
I would use a helper column on the source sheet with a nested IF to pull the appropriate dollar amount. Then you can reference this helper column in your SUMIFS.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you, @Paul Newcome ! I'm not sure I completely follow. Guess my hiccup is not being clear on how that nested IF formula should look.
-
It would look somethign like this...
=IF([Order Total]@row> 0, [Order Total]@row, IF([Quote Total]@row> 0, [Quote Total]@row, Budget@row))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome Thank you! I thought I was making progress but keep getting caught up. Also with the screenshot below, I've highlighted the cells that I want the formula to return.
=IF([Sales Order Updated]@row=1,[Balance to Invoice (EP)]@row,IF([NOT(ISBLANK([Quote Approved]@row,[Quote Total (EP)],Budget@row)
IF [Sales Order Updated] is checked, [Balance to Invoice (EP)]
IF [Quote Approved] is not blank, [Quote Total (EP)]
Value if above is false: Budget@row
What the heck am I missing here?
-
@Paul Newcome GOT IT!
=IF([Sales Order Updated]@row = 1, [Balance to Invoice (EP)]@row, IF([QUOTE Approved]@row = "*", [QUOTE Total (EP)]@row, Budget@row))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!