Need a sum formula for employee account codes

November 1, 2012 at 19:41:02
Specs: Windows XP
I am trying to create a sum formula that will search within a table and sum the total number of hours for each employees by month by account code. Every SUMIF formula I have tried comes back with an error message or it will only pick up the first row of an employee account code but if the employee have more than one account for a particular month, it omits the second and third row of account hours, if applicable. Here is a sample of my two tables:

ID # Name Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12
111 DUCK, DAFFY 10 4 7 6 11 3
222 DOE, JANE 12 0 9 7 14 6
333 DOE, JOHN 17 11 6 0 8 12


ID Name Account Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12
111 DUCK, DAFFY 1000-000 5 1 3 2 5 1
1000-100 2 1 1 2 4 1
1000-600 3 2 3 2 2 1

222 DOE, JANE 1000-100 7 0 5 4 8 4
1000-600 5 0 4 3 6 2

333 DOE, JOHN 1000-100 17 1 1 6 0 8 12

Can someone please help me?


See More: Need a sum formula for employee account codes

Report •


#1
November 2, 2012 at 12:07:24
Please click on the blue line at the end of this post and read the instructions found via that link. Then repost your data so that it is lined up by column and we can read it easier.

I'm not sure if you have posted both input and output data, so if you haven't, you should. In other words, if you give us an example of what the total should be, based on the input, we can be sure that our solution meets your requirements.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 4, 2012 at 15:42:29

INPUT WORKSHEET
ID # Name 12-Apr 12-May Jun-12 12-Jul 12-Aug 12-Sep Total
1111 BOWIE, OWIE 1000-100 3 4 7 6 11 3 37
BOWIE, OWIE 1000-600 3 3
2222 JERNI, ERNI 1000-000 12 128 128
1000-100 93.5 184 26 303.5
1000-600 172 145 56 373
3333 CITI, LITI 1000-000 120.5 120.5
1000-100 94.7 115 209.7
1000-600 135.5 168 72.5 376

So, Owie Bowie total hours for Apr-12 through Sep-12 is 37 vice 34 hours for Acct 1000-100 and 3 hours for 1000-600


OUTPUT WORKSHEET
12-Apr 12-May Jun-12 12-Jul 12-Aug 12-Sep Total
1111 BOWIE, OWIE 3 4 7 9 11 3 37
2222 JERNI, ERNI 184 145 149.5 184 128 26 816.5
3333 CITI, LITI 120.5 135.5 168 167.2 115 706.2

Report •

#3
November 4, 2012 at 15:45:51
I clicked on the
 abor the reply but I and added my data but when I preview it, it is not lined up. What am I doing wrong?

Report •

Related Solutions

#4
November 5, 2012 at 04:37:56
Once you preview it, you can edit it again to get the data lined up the way you want.

As noted in the instructions, click in the "Check To Show The Confirmation Page Again" box to preview the post again. You can do this over and over until the post in set up as you like.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question