We have a common scenario as the basis of this blog post: use Microsoft Excel to calculate sales order and invoice totals for a specific month. Though, for a recent requirement, I had a data set and a few requirements that required me to get a bit tricky with the SUMIFS and EOMONTH functions. Hopefully this quick post helps unblock you if you’re managing a similar solution.

Sales Order Invoice

Again, I’m sure this is a common scenario, but what made the situation “tricky” for me is a combination of:

• The request to calculate total sales for the month could come on any day of the month. Accounting may want to know total sales for September several times throughout the month.
• The date from which I had to determine the month in which I needed to total sales was dynamic. That is: TODAY().
• No alterations to the spreadsheet to add new columns with the start and end of the month were allowed. Therefore, I needed to calculate my date range constraints interpolated within the SUMIFS() function.

Here’s a sample spreadsheet based on the real data, and a demonstration of how it works:

Using SUMIFS and EOMONTH

Quick disclaimer before I show you this formula: in this sample, the final criteria could have been “<“&TODAY(), but for the real spreadsheet and requirements it actually needed to be “<“&EOMONTH(). So I figured I’d use the real/production formula.

Okay, so what is the formula? Here:

=SUMIFS(SalesRecords[FinalPrice],SalesRecords[CompanyName],C3,SalesRecords[OrderDate],">"&EOMONTH(B3,-1),SalesRecords[OrderDate],"<="&EOMONTH(B3,0))

• The table in the demo is “SalesRecords”.
• And, as shown in the opening of the video
  • B3 is calculated by =TODAY().
  • C3 is an input tied to a table and named range on another worksheet.
  • D3 holds the formula above.
• Criteria – with SUMIFS() all criteria must evaluate to true
  • Company name must match the selected Company in the drop-down.
  • Order date must be greater than the last day of the previous month.
  • Order date must be less than or equal to the last day of the current month.
• The key to making this function work was to use concatenation with EOMONTH to build the date range criteria.
• Effectively built, this turned out to be a pretty straightforward function depending only on SUMIFS() and EOMONTH().

Hope that helps – see you in the next post.

Categories: Business Management, Microsoft Excel, Office 365 and O365

Tags: Business Management, Microsoft Excel, Office 365 and O365