agilebiz
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
=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.
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.
I would honestly go with named ranges for this one, but I didn't feel like explaining how to do that.
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$3
regards, barry
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$3
regards, barry
There's 5 cost center columns, and 5 adjacent-related hours columns, but Barry is correct about disconnected named ranges not working.
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.
Oh wow, that is slick. Kudos to Barry on that, I didn't even recognize it when I saw it.
ASKER
Works Great and simle thx
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.