Summing numerous entries per department into one total...NO PIVOT TABLE

[apatreau]

Hi there,

I am making an project estimate spreadsheet and will have numerous resources for each department. I would like summarize the total for each department which would of course typically be done through a pivot table...only I'm finding the pivot table a little high maintenance with having to right click and refresh data.

I need this to work automatically as I will be handing this off to people who will have no idea how to work with a pivot table.

The department info I need to summarize is from G5 - G22. the total cost for that resource/department is from J5 - J22.

Any ideas?

 

[Jonmo1]

You need sumif

=SUMIF(G5:G22,"Department Name",J5:J22)

Hope that helps.

 

[VoG]

Try like this

=SUMIF(G5:G22,"Dept A",J5:J22)

 

[kidwispa]

What is "high maintenance" about 2 mouse clicks? I understand that your colleagues may not have experience/knowledge of pivot tables, but could you not just explain that in order to see the most up to date information they should right click in the pivottable and press refresh?

If that isn't a suitable option then please post an example of your data...

 

[apatreau]

What is "high maintenance" about 2 mouse clicks? I understand that your colleagues may not have experience/knowledge of pivot tables, but could you not just explain that in order to see the most up to date information they should right click in the pivottable and press refresh?

If that isn't a suitable option then please post an example of your data...

Trust me, I know. But two mouse clicks is more high maintenance than no mouse clicks.

How does one post an image on this message board? I've never been able to figure it out, it always wants a link and never a file.

 

[VoG]

Try Excel Jeanie to post a shot of your sheet http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[kidwispa]

Trust me, I know. But two mouse clicks is more high maintenance than no mouse clicks.

How does one post an image on this message board? I've never been able to figure it out, it always wants a link and never a file.

I think your question's been answered above my post...

As far as posting images are concerned, I've always uploaded my images to Photobucket, then copied the link from there...

 

[apatreau]

Try like this

=SUMIF(G5:G22,"Dept A",J5:J22)

Thanks!

It looks like I would also have to specify each department with a separate formula, correct?

for instance "QA", "PM" or "Design"?

 

[Jonmo1]

You could use a cell reference to choose which department to look at

=SUMIF(G5:G22,A5,J5:J22)

A5 = DepartmentName



P.S.

A pivot table can be configured to automatically refresh upon opening the workbook.

 

[apatreau]

You could use a cell reference to choose which department to look at

=SUMIF(G5:G22,A5,J5:J22)

A5 = DepartmentName



P.S.

A pivot table can be configured to automatically refresh upon opening the workbook.

Beautiful! thanks everyone!