Averageifs function divide by zero error

[Hr]

I trying to get the correct average of just the q1 and q3 values using averageifs function. Have looked a lot online but couldn't find a solution. However, someone kindly did help me here on MS community with a formula that works: =AVERAGE(IF(H2:H13<>"", IF(H2:H13<>0, IF((G2:G13="q1")+(G2:G13="q3")>0, H2:H13)))). They also explained that it is an OR logic type scenario.

But I am hoping to use the averageifs function (or a much smaller formula/function) to get the answer since by definition that function should work with multiple criteria even in the same column.

Some more info that may be helpful before answering my question:

Version: O365 Excel

The blank cells in the average range are empty with no values.

I have already tried this formula: =AVERAGE(AVERAGEIFS(H2:H13,G2:G13,{"q1","q3"})), but I was told it does not always work (for eg. if a value is added to any of the blank cells, the average comes incorrect).

Here is the link (because I don't have the permission to upload the images here directly and don't know how to) to the screenshot of my sheet from my OneDrive: https://1drv.ms/u/s!AmNyoq6I8cL6iWqAoPs7zfdjWREs?e=IcIoJ7

[#2]

The AVERAGEIFS function always uses AND, so all conditions in the function must be met, for a value to be counted in. To have an OR condition, you simply add two AVERAGEIFS functions:
=AVERAGEIFS(H2:H13,G2:G13,"q1")+AVERAGEIFS(H2:H13,G2:G13,"q3")
The formula will return #DIV/0! if q1 and/or q3 is not in your data.

[#3]

Hello Jan Karel Pieterse,
Thanks for your reply, now the averageifs function makes sense to me. However, I got the correct average by making some changes in your formula: =(AVERAGEIFS(H2:H13,G2:G13,"q1")+AVERAGEIFS(H2:H13,G2:G13,"q3"))/2.
Do you know if there is simpler formula/function to compute such a problem (say with more than 2 criteria) or know somewhere I can find/ask?

[#4]

Ouch, I treated this as if it were a SUMIFS, it's an AVERAGEIFS. Those -of course- cannot simply be added :-). I'm also not convinced your divide two is valid.
I see two alternatives:
=AVERAGE(IF((A1:A44="q1")+(A1:A44="q2")>0,B1:B44,""))
or
=(SUMIFS(B1:B44,A1:A44,"q1")+SUMIFS(B1:B44,A1:A44,"q2"))/(COUNTIFS(A1:A44,"q1")+COUNTIFS(A1:A44,"q2"))

[#5]

@Jan Karel Pieterse perhaps you can try divide by two yourself since I am indeed getting the correct average with that but just think the formula is too long. Also, after trying your two alternatives:

1. =AVERAGE(IF((G2:G13="q1")+(G2:G13="q3")>0,H2:H13," "))

2. =(SUMIFS(H2:H13,G2:G13,"q1")+SUMIFS(H2:H13,G2:G13,"q3"))/(COUNTIFS(G2:G13,"q1")+COUNTIFS(G2:G13,"q3"))

I get 18,847 for both, instead of the correct average which is 56,540.5.

[#6 · Accepted solution]

That is due to the empty cells in your data, to cater for those we need an additional check:
=AVERAGE(IF((H2:H13<>"")*((G2:G13="Q1")+(G2:G13="Q3"))>0,H2:H13,""))

[#7]

Ok this one works. And I now see why you are not convinced about the divide by two formula. Turns out, it returns the wrong answer if I enter a value in one of the blank cells say 123456 in H3. Just still wish there was a much shorter formula to compute this

[#8]

I'm afraid not! There might be alternatives, but I doubt if those will be much simpler. You could create a LAMBDA function for this, but I doubt whether it is worth the effort:
https://jkp-ads.com/Articles/Excel-lambda-function-basics.asp

[#9]

Ok will take a look, thanks!