< Go Back

Sum sales in last 30 days by ID

Formula

= SUMIFS(sum_range,dates,">="&TODAY()-30,ids,id)

Explanation

Let’s break down the structure of the SUMIFS function to further understand. The first 3 arguments are completely compulsory: sum_range, criteria_range, and criteria. All next arguments are optional since they represent the additional conditions, each of which takes 2 arguments.

Sum_range: the range you want to sum.

Criteria_range1: the conditional range, in this case, it is the dates.

Criteria1: the set condition of the dates. The symbol & enables you to combine a text and a function inside the argument. Since this is the sum of sales in 30 days, the required dates must be after today minus 30 days.

In case you need to sum the sales of a certain item, you may use the next 2 arguments; otherwise, they are unnecessary.

Example

Figure 1. Sum sales in 30 days with SUMIFS

In this example, you want to sum the sales in 1 month of the items on the right table G3:H5, you can do the following

  1. Select H4
  2. Insert the following formula

=SUMIFS($D$4:$D$14,$C$4:$C$14,">="&TODAY()-30,$B$4:$B$14,G4)

The function sums all amount in 1 month of the A item. The first criterium retrieves the date in column C that falls under 1 month prior and the second evaluates the amount of the item in the B column that exactly matches G4. Altogether, the criteria are met and the amount is added in the sum_range.

Notes

To calculate the sum of sales in 30 days, you can neglect the last 2 arguments to get the following formula

=SUMIFS($D$4:$D$14,$C$4:$C$14,">="&TODAY()-30)

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar