AVERAGEIFS Formula help

[hblbs]

Hello all, hope you can help.

I am having difficulty with a AVERAGEIFS formula I am currently using.

When I use filters and highlight the range that applies, excel provides to correct average calculation in the bottom RHS of the window.

However when I use the formula below it returns a completely different value that I know is wrong and am unable to work what is being included. I have used trim/clean functions and moved the sheet to a CSV file and re-import just to see if there is any hidden characters throwing it off.

=(AVERAGEIFS(Sheet3!E2:E237,Sheet3!E2:E237,"<>",Sheet3!H2:H237,{"A","B","C"}))

As Im referencing from another sheet I also use the following with the same result.

=(AVERAGEIFS(INDIRECT("'"&$AH$1&"'!"&$AK$21),INDIRECT("'"&$AH$1&"'!"&$AK$21),"<>",INDIRECT("'"&$AH$1&"'!"&$AK$19),{"A","B","C"}))

Any help is appreciated, thanks in advance.

 

[Eric W]

AVERAGEIFS really doesn't work well with arrays. What happens is you get 3 averages, 1 for A, B, and C. Then if you have AVERAGEIFS in a single cell, you only see the first average. In order to get this to work, you need to calculate the numerator (SUM) and denominator (COUNT) separately, like this:

=SUM(SUMIFS(Sheet3!E2:E237,Sheet3!E2:E237,"<>",Sheet3!H2:H237,{"A","B","C"}))/SUM(COUNTIFS(Sheet3!E2:E237,"<>",Sheet3!H2:H237,{"A","B","C"}))

The SUMIFS with the array creates 3 totals, and the SUM adds them together. Same idea with the SUM(COUNTIFS(.

Hope this clears things up some.

 

[hblbs]

Thank you for your response and guidance on this, it does appear to be working.

Could I also query a similar difficulty using the formula you've provided? It seems this works when Criteria 2 is exactly the value, but when I try to use this for any values that does not equal value the average seems to be off.

The following works

=SUM(SUMIFS(INDIRECT("'"&$AH$1&"'!"&$AK$21),INDIRECT("'"&$AH$1&"'!"&$AK$21),"<>",INDIRECT("'"&$AH$1&"'!"&$AK$16),"ABA"))/SUM(COUNTIFS(INDIRECT("'"&$AH$1&"'!"&$AK$21),"<>",INDIRECT("'"&$AH$1&"'!"&$AK$16),"ABA"))

But this does not seem to produce the correct result.

=SUM(SUMIFS(INDIRECT("'"&$AH$1&"'!"&$AK$21),INDIRECT("'"&$AH$1&"'!"&$AK$21),"<>",INDIRECT("'"&$AH$1&"'!"&$AK$16),"<>ABA"))/SUM(COUNTIFS(INDIRECT("'"&$AH$1&"'!"&$AK$21),"<>",INDIRECT("'"&$AH$1&"'!"&$AK$16),"<>ABA"))

Any help is appreciated, thank you in advance.

 

[Eric W]

I tried both your formulas, and I get the results I'd expect. Could you show a sample of data that doesn't return your expected result, and explain what you think it should be?

Also, as an FYI, you don't need the SUM around the SUMIFS and COUNTIFS unless you're using an array to specify multiple values. It doesn't hurt either though, so you could leave it to make it easier to modify later. But without an array, you could also just use an AVERAGEIFS.