Formula to SUM from Multiple Columns with IF Statement

Options

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 -

  1. $ are "bucketed" to the Customer Roll-Up 1 sheet into the Month they are projected to invoice ("INVOICING Projected Date")
  2. If "Quote Approved" is blank, sum BUDGET
  3. If "Quote is Approved", but "Order Updated" is NOT checked, sum QUOTE Total
  4. 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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!