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

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

# 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
1 Solution

Programmer AnalystCommented:
VLookup will do what you need.  You can just have separate vlookup function for each cost center.

Let me know what you think and I can help you through it if you need.
0

Project 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

Commented:
``````=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)
``````

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

Project 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

Commented:
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

Project 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

Commented:
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

Project Engineer, ElectricalCommented:
Oh wow, that is slick.  Kudos to Barry on that, I didn't even recognize it when I saw it.
0

Author Commented:
Works Great and simle thx
0

## Featured Post

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