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
