As long as you want only these fairly simple calculations,
you can use the AVERAGEIFS
, COUNTIFS
, and SUMIFS
functions.
COUNTIFS
simply counts the cells in a range that meet specified criteria.
For example,
=COUNTIFS(A2:A5, "foo")
returns the number of occurrences of foo
in column A.
(This can be expanded to test multiple conditions, including <
, <=
, >
, and >=
for numbers.)
SUMIFS
adds (sums) cells
where other cell(s) in the same row (or column) meet specified criteria.
For example,
=SUMIFS(C2:C5, B2:B5, "cherry")
adds the values in column C that correspond to cherry
in column B.
AVERAGEIFS
works the same way, so your first problem can be done as
=AVERAGEIFS(C2:C5, A2:A5, "foo")
or you can just divide the sum by the count.
There are also AVERAGEIF
, COUNTIF
, and SUMIF
functions that do essentially the same thing,
but they don’t support multiple criteria, and their calling sequences are incompatible.