AVERAGEIFS with date range and category
IntermediateSales reports often need an average for just one slice of the pipeline, such as one product category inside one quarter. AVERAGEIFS is built for that kind of filtered analysis.
AVERAGEIFS returns the average of a range, but only for rows that meet every condition you include.
The syntax looks like:
=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
When you filter by dates, you can compare a date column to parameter cells by joining an operator to the cell reference, such as ">="&X1 or "<="&Y1.
Your task:
- Review the CRM export in A1:D13. The values to average are in the Deal amount column.
- Use the quarter boundary cells in B15:B16 as your date criteria.
- In cells B18:B20, calculate the average deal amount for each product category listed in A18:A20.
Your formulas should average only deals that match the category in column A and have a close date within the quarter start and quarter end dates.
Need some help?
Hint 1
AVERAGEIFS needs three criteria pairs here: one for the product category, one for dates on or after the quarter start, and one for dates on or before the quarter end.
Hint 2
Date comparisons use operators joined to cell references, for example `">="&A1` (if the start date is in A1) for the start date test and `"<="&B1` (if the end date is in B1) for the end date test.
Related function(s)
AVERAGEIFS with date range and category
IntermediateSales reports often need an average for just one slice of the pipeline, such as one product category inside one quarter. AVERAGEIFS is built for that kind of filtered analysis.
AVERAGEIFS returns the average of a range, but only for rows that meet every condition you include.
The syntax looks like:
=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
When you filter by dates, you can compare a date column to parameter cells by joining an operator to the cell reference, such as ">="&X1 or "<="&Y1.
Your task:
- Review the CRM export in A1:D13. The values to average are in the Deal amount column.
- Use the quarter boundary cells in B15:B16 as your date criteria.
- In cells B18:B20, calculate the average deal amount for each product category listed in A18:A20.
Your formulas should average only deals that match the category in column A and have a close date within the quarter start and quarter end dates.
Need some help?
Hint 1
AVERAGEIFS needs three criteria pairs here: one for the product category, one for dates on or after the quarter start, and one for dates on or before the quarter end.
Hint 2
Date comparisons use operators joined to cell references, for example `">="&A1` (if the start date is in A1) for the start date test and `"<="&B1` (if the end date is in B1) for the end date test.