# AVERAGEIF get a column by a month name in its header

Basically, I have a table with a bunch of people names vertically and months listed horizontally. Then I have a drop-down list of all months. For the purposes of `=AVERAGEIF` I want to use that drop-down list to point to a corresponding column without making 12 different named ranges for every page with the same table I'll need to make in the future.

An example:
I want something like `=AVERAGEIF(name_range; Greta; March)` when `March` is selected in a drop-down, which will return an average between \$2557 and \$3367.

• Hi and welcome to WebApps. Would you explain how the value = `Greta` is determined if there is only one set dropdowns (Month)? Are you sure that you're not assuming a dropdown for "Names" as well? Jan 7 at 1:02
• Hello, @Tedinoz I will be using requested formula on another sheet with a table of names made with `=unique(name_range)`. Jan 7 at 19:06
• Hi. OK - 2 things: 1) `=unique(name_range)` will only deliver a list on names, it won't enable you to select one. That's why I think you need the dropdown (or perhaps that is what you intended). FWIW, a dropdown will generate a list of unique values, so you wouldn't need to use `UNIQUE`. 2) For future reference, your explanation: I will be using requested formula on another sheet with a table of names made with =unique(name_range) is pretty important and is the type of thing that should be included in the body of the question. Jan 8 at 0:26

There are likely to be many ways to answer this question. However you have specifically cited `AVERAGEIF`.

There is an existing dropdown to identify the Month.

• There needs to be an additional dropdown to identify the Name.

• With those pieces of data, try this formula `=averageif(A2:A6;H2;indirect(address(2;month(\$I\$2&1)+1;4)&":"&address(6;month(\$I\$2&1)+1;4)))`

• Or for those in a locale that uses a decimal point `=averageif(A2:A6,H2,indirect(address(2,month(\$I\$2&1)+1,4)&":"&address(6,month(\$I\$2&1)+1,4)))`

Sample

Logic

• `criteria range` = A2:A6 but you can sustitute your named range`
• `criterion` = value of the "Name" dropdown
• `[average range]` = supplied by `INDIRECT`
• range based on `address` function
• Row: Start:2, End:6
• Column: `month(\$I\$2&1)+1` this converts the string value of the Month dropdown into a number. Add 1 because the function is zero-based.
• It works! Yeah, I was finally resigned to using named ranges. But then I had understood why this formula didn't work for the first time - my locale. In my locale, a semicolon `;` is a divider, so all formulae using a simple comma `,` will refuse to work. Jan 7 at 19:04
• My bad. I did notice the locale aspect of your formula. Anyway, I've edited the answer to include added both versions. Jan 8 at 0:33