Solved

# Sum cells in excel

Posted on 2012-08-15
355 Views
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
Question by:agilebiz

LVL 15

Expert Comment

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

LVL 13

Accepted Solution

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

LVL 4

Expert Comment

``````=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

LVL 13

Expert Comment

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

LVL 50

Expert Comment

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

LVL 13

Expert Comment

There's 5 cost center columns, and 5 adjacent-related hours columns, but Barry is correct about disconnected named ranges not working.
0

LVL 4

Expert Comment

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

LVL 13

Expert Comment

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

Author Closing Comment

Works Great and simle thx
0

## Featured Post

### Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.