Sum sales in last 30 days by ID

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:

=SUMIFS($D$5:$D$104,$C$5:$C$104,">="&TODAY()-30,$B$5:$B$104,F5)

How this formula works

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

=SUMIFS($D$5:$D$104

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:

=SUMIFS($D$5:$D$104,$C$5:$C$104,">="&TODAY()-30

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

=SUMIFS($D$5:$D$104,$C$5:$C$104,">="&TODAY()-30,$B$5:$B$104,F5)

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:

SUMIFS(amounts,dates,">="&TODAY()-30)

For the example above, this becomes:

SUMIFS($D$5:$D$104,$C$5:$C$104,">="&TODAY()-30)