Excel advanced conditional average

0
votes

I have the following issue using Excel.

I have a table with thousands of rows. Each row represents a room in a construciton site. There are 4 columns associated with the region [ie. building complex, building, floor, floor department]. The rest of the columns are details, such as room area. Example here:

http://1drv.ms/1NAB61e

What I want to do, is to find for each row the room area average of the room's region. I can't use sumif and countif, because I don't have only one region column but 4. So how can I get over with this?

1
excelconditionalaverage
I think you should add some expected results to your example so that it is clear what is being sought. - XOR LX
It's better to either lay out your data in text format or upload a picture (to a known image hosting site such as imgur.com), rather than have a link to a lesser-known site. You'll be more likely to get help if you make it easy for people to do nothing to see your situation. However, all that being said, if you understand SUMIF and COUNTIF, look at SUMIFS and COUNTIFS. Allows you to use multiple criteria. - Grade 'Eh' Bacon

1 Answers

0
votes

To elaborate on my comment above:

If column A includes values, and columns B, C, and D have criteria, you can find the average value in column A for all rows that match criteria B1, C3, and D5 with the following formula:

=SUMIFS(A:A,B:B,B1,C:C,C3,D:D,D5)/COUNTIFS(B:B,B1,C:C,C3,D:D,D5)

SUMIFS / COUNTSIFS simply allow you to continue to add ranges (always of the same size) and criteria to the right side of your formula. Note that for SUMIFS, the arrangement is different. In SUMIF, the column to be checked for criteria is the left-most argument; in SUMIFS, the column to be summed is the left-most argument.