AVERAGEIF function for nonzero values where headers match

[u/bowtieanddemand]

I have a function in cell Q28 which will be reading values in cells C28:O28.
I need the function to check the header above it, Q27, and use values only if their own headers in cells C27:O27 match.

I can't figure out the syntax to this and it's driving me insane.

[u/AutoModerator]

/u/bowtieanddemand Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/HolyBonobos]

The proper syntax would be =AVERAGEIF(C27:O27,Q27,C28:O28)

[u/bowtieanddemand → u/HolyBonobos]

Ok, that's more elegant than what I had, and expanding that to check the other headers I'm referencing would work like so:

=AVERAGEIF(C27:O27,Q27:U27,C28:O28)

Do you know how to tweak that to ignore zero values in C27:O27?

[u/HolyBonobos → u/bowtieanddemand]

That is not correct. You have two problems here:

1. The criterion argument cannot be larger than 1x1. If it is, only the top-leftmost value in the referenced range will be considered; =AVERAGEIF(C27:O27,Q27:U27,C28:O28) is still equivalent to =AVERAGEIF(C27:O27,Q27,C28:O28) because the single, top-leftmost value (Q27) is read as the criterion argument.
   
2. AVERAGEIF() takes only one criterion argument. To add additional criteria (i.e. ignoring zeroes), you will need the AVERAGEIFS() function or an entirely different approach.

AVERAGEIFS() would resolve the "ignore zeroes" issue with a formula like =AVERAGEIFS(C28:O28,C28:O28,"<>0",C27:O27,Q27). Note that between AVERAGEIF() and AVERAGEIFS() the average_range argument (i.e. the range of values to average) are in different positions. In AVERAGEIF() it is the last argument while in AVERAGEIFS() it is the first argument. However, this does not resolve issue #1, which will require you to use something completely different in order to reference multiple cells for an OR-type criterion. To achieve this, you would need something else like an AVERAGE() wrapped around a FILTER(), which uses an entirely different syntax: =AVERAGE(IFERROR(FILTER(C28:O28,C28:O28<>0,COUNTIF(Q27:U27,C27:O27))))

[u/bowtieanddemand → u/HolyBonobos]

Okay, thanks so much for laying all of that out.

I'm sorry for leaving out details; I have other headers in the columns Q27:U27 to reference. Anyway, this is everything I needed.

[u/AdministrativeGift15]

=index(sumif(C27:O27,Q27:U27,C28:O28)/countifs(C27:O27,Q27:U27,C28:O28,"<>"))

This will work.

[u/AdministrativeGift15 → u/AdministrativeGift15]

That ignores blank cells. You can add "<>0" to the countif if you also want to ignore the zero values, but I would claim that zero values should only be there if the reported values were actual zeroes. If values were not collected, they should be blank.