Sum Monthly Sales with Multiple Variables

[ktg0011]

Hello,

See attached spreadsheet. That's probably the best way to explain what I am looking for. I can't quite figure out how to get everything that I want included in one formula.

Any help is appreciated!
Thanks

 

[ktg0011]

To clarify, I am looking for a sum of 2017 sales. There are some 2018 dates in the spreadsheet that I do not want included.

 

[r2c2]

@ktg0011

Interesting question. The easiest way to get the answer you want:

• Add a column to your original data to calculate which of the three amounts to be used. A simple if formula like =IF([@INVOICED]<>0,[@INVOICED],IF([@[REV AMT]]<>0,[@[REV AMT]],[@NET])) would do the trick.
• Once such a column is added, simply create a pivot table with office on columns, date (grouped by month) on rows and this new column in values area. That simple.
• If you wish to solve thru formulas, you can also use SUMIFS on this new column like below.
• =SUMIFS(data[Sum col],data[START DATE],">="&DATEVALUE("1-"&$A2&"-2017"), data[START DATE],"<="&EOMONTH(DATEVALUE("1-"&$A2&"-2017"),0),data[OFFICE],E$1)

But if you like pain and long winded routes, you can use a variation of sumproduct to get the answer from just the data you have provided. Like below:
=SUMPRODUCT(IF(data[INVOICED]<>0, data[INVOICED], IF(data[REV AMT]<>0,data[REV AMT], data[NET])),(data[START DATE]>=DATEVALUE("1-"&$A3&"-2017"))*(data[START DATE]<=EOMONTH(DATEVALUE("1-"&$A3&"-2017"),0))*(data[OFFICE]=E$1))

Array formula.

Column A has month name
Row 1 (E1 onwards) has office number

See attached workbook with both kinds of formulas. The yellow cell has sumproduct.

All the best.

 

[droopy]

See attached example.

Sumproduct, but with no array formula (and no added column). I changed your Sheet2 range to Table for flexibility.
Be careful that in your sheet2, sometimes there is a net and a invoiced, but no rev amt. In this case nothing is added, as I exactly applied your request (i.e. sum invoiced only if there is net and rev amt and invoiced).

• Net will be added if both rev amt and invoiced are 0 => (Table1[NET]*(Table1[REV AMT]=0)*(Table1[INVOICED]=0).
  
• Rev Amt will be added if net is not 0, and invoiced is 0 => (Table1[REV AMT]*(Table1[NET]<>0)*(Table1[INVOICED]=0))
  
• Invoiced will be added if none of them is 0 => (Table1[INVOICED]*(Table1[NET]<>0)*(Table1[REV AMT]<>0))

This will happen only when

• the office # is matching the office #of the column => --(Table1[OFFICE]=VALUE(RIGHT(E$1,1))), where -- will cast the TRUE/FALSE value to 1 / 0
• The month is matching the month in the row => --(TEXT(Table1[START DATE],"mmmm")=$A2))

Full formula for cell E2:

=SUMPRODUCT((Table1[NET]*(Table1[REV AMT]=0)*(Table1[INVOICED]=0))+(Table1[REV AMT]*(Table1[NET]<>0)*(Table1[INVOICED]=0))+(Table1[INVOICED]*(Table1[NET]<>0)*(Table1[REV AMT]<>0)),
--(Table1[OFFICE]=VALUE(RIGHT(E$1,1))),
--(TEXT(Table1[START DATE],"mmmm")=$A2)).

 

[ktg0011]

See attached example.

Sumproduct, but with no array formula (and no added column). I changed your Sheet2 range to Table for flexibility.
Be careful that in your sheet2, sometimes there is a net and a invoiced, but no rev amt. In this case nothing is added, as I exactly applied your request (i.e. sum invoiced only if there is net and rev amt and invoiced).

• Net will be added if both rev amt and invoiced are 0 => (Table1[NET]*(Table1[REV AMT]=0)*(Table1[INVOICED]=0).
  
• Rev Amt will be added if net is not 0, and invoiced is 0 => (Table1[REV AMT]*(Table1[NET]<>0)*(Table1[INVOICED]=0))
  
• Invoiced will be added if none of them is 0 => (Table1[INVOICED]*(Table1[NET]<>0)*(Table1[REV AMT]<>0))

This will happen only when

• the office # is matching the office #of the column => --(Table1[OFFICE]=VALUE(RIGHT(E$1,1))), where -- will cast the TRUE/FALSE value to 1 / 0
• The month is matching the month in the row => --(TEXT(Table1[START DATE],"mmmm")=$A2))

Full formula for cell E2:

=SUMPRODUCT((Table1[NET]*(Table1[REV AMT]=0)*(Table1[INVOICED]=0))+(Table1[REV AMT]*(Table1[NET]<>0)*(Table1[INVOICED]=0))+(Table1[INVOICED]*(Table1[NET]<>0)*(Table1[REV AMT]<>0)),
--(Table1[OFFICE]=VALUE(RIGHT(E$1,1))),
--(TEXT(Table1[START DATE],"mmmm")=$A2)).

Thank you very much. Is there a way to include the invoiced amount if there is no revised amount?

 

[droopy]

Thank you very much. Is there a way to include the invoiced amount if there is no revised amount?

In the sub-formula below:

• Invoiced will be added if none of them is 0 => (Table1[INVOICED]*(Table1[NET]<>0)*(Table1[REV AMT]<>0))
  

the blue amount will be summed only when the purple amount is not 0 (or empty), and when the green amount is not 0 or empty. This is because the (Table1[NET]<>0) will return TRUE or FALSE for each array item, and this TRUE/FALSE value is evaluated as 1/0 when multiplied to another item. And the same will happen with Rev_Amt. So if, for example, net is not 0 and rev_amt is empty or 0, you will end up with the value of Table1[invoiced] * 1 * 0 - which results in 0.
If you don't want to check the condition on Net value or on Rev_Amt value, you can simply remove that condition.
So if you want to sum the invoiced amount regardless of both net an rev_amt, you will only keep (Table1[INVOICED]) from the formula above.

If you want to sum the invoiced amount only if net is not 0 or empty, you'll change
(Table1[INVOICED]*(Table1[NET]<>0)*(Table1[REV AMT]<>0))
to (Table1[INVOICED]*(Table1[NET]<>0))

And if you want to sum the invoiced amount only if rev_amt is not 0 or empty, you will change
(Table1[INVOICED]*(Table1[NET]<>0)*(Table1[REV AMT]<>0)) to (Table1[INVOICED]*(Table1[REV AMT]<>0))

You will keep the other parts of the formula unchanged.