0

I'd like to calculate the average spending on given categories but not all. I use AVERAGEIFS for this. But it doesn't seem to work.

My sheet (just relevant columns):

  B  |   D
Spent|Category
-----|---------
10   |Category1
11   |Category1
9    |Category6
30   |Category3
22   |Category4
15   |Category5

I have this formula:

=AVERAGEIFS(B2:B;D2:D;"Category1";D2:D;"Category2";D2:D;"Category3";D2:D;"Category4";D2:D;"Category5")

And it always gives me #DIV/0! Even when I have at least one value in all category. Based on the documentation I either do this right or I fail to see the error in my ways.

0
3

Like @pnuts said in his comment, all AVERAGEIFS criteria must match. You seem to need all these values where one or more of the criteria is matching. This formula should give you what you want:

=AVERAGE(FILTER(B2:B,MATCH(D2:D, {"Category1","Category2","Category4"}, 0)))
1
  • Thank you. This works. I didn't understand it had to match everything. May 19 '17 at 5:46
1

This might meet your requirement while being a little shorter:

=(sum(B:B)-sumif(D:D,"Category6",B:B))/(countif(D:D,"<>Category6")-1-countblank(D:D))
1
  • 1
    I take note of this, because it will come handy at some point but I have to exlude more than one category and it is easier to include those specific ones. May 19 '17 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.