None of the existing questions/answers seem to answer my question, so hopefully there's someone who can help me solve my issue. I'm not super familiar with all of the Excel functions, so five me if I'm not understanding it right based on the formulas I've tried.
In summary, I am trying to create a formula for cells in Tab A to find the average of a column of cells in Tab B based on certain criteria. From what I can tell, I need to use the AVERAGEIFS statement to do this, but I can't get it to work properly (it keeps showing the #DIV/0! error).
Cells to Use to Find the Average: Tab B, range L200:L221 (this is a column that averages amounts in other cells within Tab B, so this column already has a formula in it)
Criteria:
- Tab B: Range B200:B221 matches a specific cell on Tab A in column A (ex. A46 in Tab A)
- Tab B: Range E200:E221 is between a certain date range (ex. the entire 2025 calendar year)
- Tab B: Range G200:G221 is between 1 to 5
All criteria must be met, not just one or two
In detail, Tab B (Requested Quotes - LTL) is used to gather different freight rates I receive for different locations. It details the State (column B) (criteria 1 above), the date the rate was received (column E) (criteria 2 above), and the number of pallets the rate is for (column G) (criteria 3 above). Tab A (2025 Average Rates - Data) is used to summarize this data from Tab B into a single cell for each state.
I have tried the following formula with no success:
=AVERAGEIFS('Requested Quotes - LTL'!L200:L221,'Requested Quotes - LTL'!B200:B221,'2025 Average Rates - Data'!A46,'Requested Quotes - LTL'!E200:E221,">=1/1/2025",'Requested Quotes - LTL'!E200:E221,"<=12/31/2025",'Requested Quotes - LTL'!G200:G221,">=1",'Requested Quotes - LTL'!G200:G221,"<=5")
None of the existing questions/answers seem to answer my question, so hopefully there's someone who can help me solve my issue. I'm not super familiar with all of the Excel functions, so five me if I'm not understanding it right based on the formulas I've tried.
In summary, I am trying to create a formula for cells in Tab A to find the average of a column of cells in Tab B based on certain criteria. From what I can tell, I need to use the AVERAGEIFS statement to do this, but I can't get it to work properly (it keeps showing the #DIV/0! error).
Cells to Use to Find the Average: Tab B, range L200:L221 (this is a column that averages amounts in other cells within Tab B, so this column already has a formula in it)
Criteria:
- Tab B: Range B200:B221 matches a specific cell on Tab A in column A (ex. A46 in Tab A)
- Tab B: Range E200:E221 is between a certain date range (ex. the entire 2025 calendar year)
- Tab B: Range G200:G221 is between 1 to 5
All criteria must be met, not just one or two
In detail, Tab B (Requested Quotes - LTL) is used to gather different freight rates I receive for different locations. It details the State (column B) (criteria 1 above), the date the rate was received (column E) (criteria 2 above), and the number of pallets the rate is for (column G) (criteria 3 above). Tab A (2025 Average Rates - Data) is used to summarize this data from Tab B into a single cell for each state.
I have tried the following formula with no success:
=AVERAGEIFS('Requested Quotes - LTL'!L200:L221,'Requested Quotes - LTL'!B200:B221,'2025 Average Rates - Data'!A46,'Requested Quotes - LTL'!E200:E221,">=1/1/2025",'Requested Quotes - LTL'!E200:E221,"<=12/31/2025",'Requested Quotes - LTL'!G200:G221,">=1",'Requested Quotes - LTL'!G200:G221,"<=5")
1 Answer
Reset to default 0Your first criteria '2025 Average Rates - Data'!A46, as writen, is not a criteria, just a cell reference. Try writing instead "="&'2025 Average Rates - Data'!A46 to test for the equality.
With the data you told me I created the following tab in Excel and named it Requested Quotes
I also created the Average Rates tab as follows:
A1 plays the role of your A46 and the formula is in A3. It gives the correct results. The formula gives the desired results, when TX is in A1 as well as when ON is, and is writen as before (but the range number have been changed to the ones I created)
=AVERAGEIFS('Requested Quotes'!L2:L8,'Requested Quotes'!B2:B8,"="&'Average Rates'!A1,'Requested Quotes'!F2:F8,"=2025", 'Requested Quotes'!G2:G8, ">=1", 'Requested Quotes'!G2:G8, "<=5")