Quick, clean, and to the point

Sum sales in last 30 days by ID

Excel formula: Sum sales in last 30 days by ID
Generic formula 

To sum sales in the last 30 days by an id (like name, initials, region, etc.) you can use the SUMIFS function together with the TODAY function. In the example shown, the formula in G5, copied down, is:


The SUMIFS function can handle multiple criteria when calculating a sum. In this case, SUMIFS is configured with a sum range for all amounts:


The first criteria specifies a criteria range that contains all dates, with a criteria of greater than or equal to the current date minus 30:


The second criteria specifies a criteria range of all IDs, with a criteria of a single ID pulled from column F:


Note ranges for amounts, dates, and ids are absolute references, so that they won't change when copied. When the formula is copied down column G, SUMIFS calculates total sales in the last 30 days for each ID in the table.

Total sales in the last 30 days

To get a sum for total sales in the last 30 days, just omit the second criteria:


For the example above, this becomes:

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas