SUMIF function
Posted by Diane on June 26, 2001 1:59 PM
I am hoping someone can tell me if this is possible and if it is...could you help me?!
My excel workbook consists of 2 worksheets.
Sheet1 consists of timesheet entries similar to below
example:
A B C D
1 Date initials code hours
2 03/03/2001 abc 4000 3.5
3 03/03/2001 abc 4000 2.0
4 03/04/2001 abc 6500 6.5
5 03/04/2001 abc 7000 1.0
6 03/03/2001 def 1000 3.0
7 03/03/2001 def 2000 1.0
8 03/04/2001 def 1000 2.0
Then on my second sheet I have each employee per row and date per coloumn
example
A B C D
Name initals 03/03/2001 03/04/2001
1Andrea Clark abc help help
2Dale Franks def
3Jim Taylor jrt
I need to format my SUMIF function to add up hours by employee by day. Can you and and criteria with the Sumif function??
When I use the SUMIF function I ether add up all hours that match that day OR I end up adding all hours by initials.
Is there way to do this and I am approaching this all wrong or have I spent way too much time on this???
Posted by Ben O. on June 26, 2001 2:17 PM
We love conditional sum questions here! :) Here's how I would do it. Enter this formula in C2 on your second sheet:
=SUM(IF(Sheet1!$B$2:$B$8=$B2,IF(Sheet1!$A$2:$A$8=C$1,Sheet1!$D$2:$D$8,0),0))
It is an array formula, so you have to Ctrl + Shift + Enter it. Change the 8's in the formula to the number of the last row of your data (or higher). You should be able to fill the formula across and down on your second sheet.
-Ben
Posted by Russell on June 26, 2001 2:26 PM
This is an excellent case for a pivot table. You could combine your name information with your initials info, or you could make your pivot table off of just the information containing initials, and then do a vlookup to the pivot table.
If you would like, email me a sample spreadsheet with about 20 records, and I'll show you what I mean.
Russell I am hoping someone can tell me if this is possible and if it is...could you help me?!
