Sum If - Finding Average for similar company name

[Zan03]

Hi All,

My spreadsheet is calculating monthly revenue per building and averaging that cost in column p.

Each building belongs to a different company.

I would like to calculate the average of all buildings belonging to each company.

Example. I would need to average the cost of all buildings belonging to company “1”. I would need to average the “averages” in column “P” for rows 2, 3, 4 and 5. I would need to do this for 2000 buildings/1000 companies.

Any suggestions?

[NBVC]

Probably Pivot tables is your best bet..

You can drag Company and Building over to Row Data and then Revenue to Data area.

Then select Average instead of Sum for your field setting.

[Zan03]

I used the following formula to gather the SUM of all similar companies.... isn't there a way to manipulate that formula to gather average costs rather than sum?

=SUMIF('Master Database'!$B$2:$B$2000,A81,'Master Database'!$BG$2:$BG$2000)

Thanks

[NBVC]

I assumed you did not yet have a consolidated list of buildings...

Try:

=SUMIF('Master Database'!$B$2:$B$2000,A81,'Master Database'!$BG$2:$BG$2000)/COUNTIF('Master Database'!$B$2:$B$2000,A81)

[Zan03]

That works perfectly.

Thanks!

[NBVC]

Please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save