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.
-
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))
-
@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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!