Formula to SUM from Multiple Columns with IF Statement

KGetchell

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

Paul Newcome

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.

KGetchell

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.

Paul Newcome

It would look somethign like this...

=IF([Order Total]@row> 0, [Order Total]@row, IF([Quote Total]@row> 0, [Quote Total]@row, Budget@row))

KGetchell

@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?

KGetchell

@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))