CALC. How to apply a function in cells where another cell in the row has a specific value

[kiko35]

Hi! Sorry if this was already asked. I know it’s very basic, but I’m not able to solve it and didn’t find a post about it.

I want to know the AVERAGE of the cells in column ‘value’ depending on the ‘name’. Until now, I used to order column ‘Name’, so =AVERAGE(B1:B3) for µ and B4:B5 for ß, but I need to order my data constantly using other columns. Therefore, I would like to know how to select cells depending of the value of another cell in the same row. Something like AVERAGE in B1:B5 (the whole spreadsheet) WHERE column A = µ.

Could you help me?
Thanks!

A: NAME   B: VALUE
µ         50
ß         90
µ         10
µ         7
ß         65

[Lupp]

=AVERAGEIFS(B1:B5;A1:A5;"µ") or even
=AVERAGEIFS(B:B;A:A;"µ") if there is nothing else but your criterion strings in column A.
AVERAGEIFS() accepts many pairs of a range and a criterion. The range of values to take the average of is given as the first parameter.
There is also the function AVERAGEIF(), but I would deprecare it because the range of averages is in the last place there.
Howver, AVERAGEIFS() is not available in very old versions. Also old versions and still the AOO don’t accept the complete-column-notation like A:A.

There are the respective multiple-criteria-functions SUMIFS() and COUNTIFS(), but to get applied an arbitrary function under a criterion-like condition requires explicit constructs, and most likely the forcing of array-evaluation. In some cases the SUMPRODUCT() function can help to find a workaround.

[kiko35]

Thank you!!