[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

Sum cells in excel

I have a monthly time sheet that I select a different cost center(which are in a dropdown list) on every day of the month to bill my time to, each day can have up to 5 different cost centers. Beside the cost center I input the amount of hours billed (seperate cell).

What I need to do is sum all the hours associated with each cost center, so scan the spread sheet based upon the information in the dropdown list then either place the totals in a seperat column or a seperate sheet on the file.

I am going to attach a file so it will be easier for everyone to understand.

Any help would be great
TimeSheet12-2012.xlsx
0
agilebiz
Asked:
agilebiz
1 Solution
 
David L. HansenProgrammer AnalystCommented:
VLookup will do what you need.  You can just have separate vlookup function for each cost center.

Here is a good example...http://spreadsheets.about.com/od/excelfunctions/ss/vlookup.htm

Let me know what you think and I can help you through it if you need.
0
 
RyanProject Engineer, ElectricalCommented:
Most obvious way is 5 SUMIfs

T1=SUMIF(C9:C39,$Q1,D9:D39)+SUMIF(E9:E39,$Q1,F9:F39)+SUMIF(G9:G39,$Q1,H9:H39)+SUMIF(I9:I39,$Q1,J9:J39)+SUMIF(K9:K39,$Q1,L9:L39)

A cleaner way would be to set 2 named ranges that has all 5 columns for CC and one for Hours, then use 1 sumif function.
0
 
DustinKikuchiCommented:
=SUMIF($C$9:$C$39,Q1,$D$9:$D$39)+SUMIF($E$9:$E$39,Q1,$F$9:$F$39)+SUMIF($G$9:$G$39,Q1,$H$9:$H$39)+SUMIF($I$9:$I$39,Q1,$J$9:$J$39)+SUMIF($K$9:$K$39,Q1,$L$9:$L$39)

Open in new window


MrBullwinkle's approach is the one I was working out as well, except that I'm fairly sure you want the ranges to be reference locked as opposed to the query cells.

If you paste this into T1 you will be able to copy the formula down.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
RyanProject Engineer, ElectricalCommented:
Ahh yes, Dustin is correct.

I would honestly go with named ranges for this one, but I didn't feel like explaining how to do that.
0
 
barry houdiniCommented:
It's possible to define a named range which encompasses 5 discrete ranges.....but you won't be able to use that range in a SUMIF function, SUMIF needs a single continuous range

As you will presumably only have cost centres in the cost centre column you can use one SUMIF which uses a multi-column range, like this in T1 copied down

=SUMIF(C$9:K$39,Q1,D$9:L$39)

regards, barry
0
 
RyanProject Engineer, ElectricalCommented:
There's 5 cost center columns, and 5 adjacent-related hours columns, but Barry is correct about disconnected named ranges not working.
0
 
DustinKikuchiCommented:
His formula actually works as well and is certainly much shorter.  Something I've never thought to try before but I'm always happy to learn new shortcuts.
0
 
RyanProject Engineer, ElectricalCommented:
Oh wow, that is slick.  Kudos to Barry on that, I didn't even recognize it when I saw it.
0
 
agilebizAuthor Commented:
Works Great and simle thx
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now