Share via

Average if formula

Anonymous
2024-11-28T15:32:59+00:00

Hi,

I have an averageif formula in my spreadsheet which works fine. The formula is on tab called summary, and is as follows:

AVERAGEIF(Sheet1!G20:G26, "Y", Sheet1!F20:F26)

What i would like though, is the average score across multiple tabs, not just sheet1 f20:f26.

It would be average from:

Sheet1 f20:f26

Sheet2 f20:f26

Sheet3 f20:f26

All where g20:g26 on each table is Y

Is this possible?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-11-28T16:24:28+00:00

    Thank you for the quick reply.

    In my range, if I referenced the full line would that then allow for different ranges on each table?

    So rather than listing sheet1, sheet2 sheet3 it would actually say

    sheet1!g20:g26

    Sheet2!g20:g29

    Sheet3!g20:g35

    Was this answer helpful?

    0 comments
  2. HansV 462.6K Reputation points
    2024-11-28T16:04:12+00:00

    Create a list of the sheet names (Sheet1, Sheet2, Sheet3) in a range of cells.

    Name this range Sheets.

    =LET(crt, INDIRECT("'"&Sheets&"'!G20:G6"), rng, INDIRECT("'"&Sheets&"'!F20:F26"), SUM(SUMIF(crt, "Y", rng))/SUM(COUNTIF(crt, "Y")))

    Was this answer helpful?

    0 comments