Lookup Function & Sums

[eizyman]

Hi,

I have a workbook with the following characteristics. The first worksheet is a summary worksheet.

Excel Workbook
	A	B	C	D	E
1	2011 Attendance Tracking, Year-to-Date Summary	*	*	*	*
2	*	*	*	*	*
3	*	*	*	*	*
4	Last Name	First Name	Benefit Type	STD/FMLA Hours	STD Hours
5	Reed	Jeffrey	70% STD	*	*
6	TestA	*	*	*	*
7	TestB	*	*	*	*
8	TestC	*	*	*	*
9	TestD	*	*	*	*
10	TestE	*	*	*	*
11	TestF	*	*	*	*
12	*	*	*	*	*
13	*	*	*	*	*
14	*	*	*	*	*
Year-to-Date Summary

Then I have a worksheet for each month of the year setup the exact same as the summary worksheet above. The process is to enter a persons name on the summary worksheet, then enter that same persons name in the given month where they are on short term disability. The person could be on short term disability for many months or just a single month and the cell they are in the given months will be different.

I'm looking for a formula that will look at the names in the summary worksheet, go out to all the individual month worksheets, sum up the total hours in column D for that person and return it on the summary worksheet. So for example if TestA has short term disability in Feb, Mar, & Apr and Feb was 12 hrs, Mar as 4 hrs, and Apr was 40 hours, the summary tab would show 56 hours in cell D6.

Any help would be appreciated.

Steve

 

[eizyman]

I know there is an Excel expert outthere somewhere that has the right answer to this...anyone?

Pretty please?

 

[eizyman]

Ok, I've tried using a simple index fuction and can get it to return the value from the January worksheet, but when I try to add the worksheets together, I get an #N/A.

=(INDEX(January!C5:C35,MATCH('Year-to-Date Summary'!A6,January!A5:A35,0))+(INDEX(February!C5:C35,MATCH('Year-to-Date Summary'!A6,February!A5:A35,0))+(INDEX(March!C5:C35,MATCH('Year-to-Date Summary'!A6,MarchA5:A35,0)))))

I would think this would just sum them together?

 

[David20]

First, your formula is looking for the hours in column C. Based on what you wrote and your layout, I assumed you intend to enter monthly hours in column D.

All you missed was to account for an error (which occurs if a name is not on a monthly sheet). Adding IFERROR prior to each INDEX solves your problem. Paste this into D5 and copy it all the way down to D35.

=IFERROR(INDEX(January!$D$5:$D$35,MATCH('Year-to-Date Summary'!A5,January!$A$5:$A$35,0)),0)+IFERROR(INDEX(February!$D$5:$D$35,MATCH('Year-to-Date Summary'!A5,February!$A$5:$A$35,0)),0)+IFERROR(INDEX(March!$D$5:$D$35,MATCH('Year-to-Date Summary'!A5,March!$A$5:$A$35,0)),0)

Second, the above formula will only work if you don't have any duplicate last names. I would change column A to contain full names (ex: Last, First).

If that isn't possible, you can insert a new column C, put

=if(A5="","",CONCATENATE(A5,", ",B5))

in the new column C, and change your formula (which is now in column E) to

=IFERROR(INDEX(January!$E$5:$E$35,MATCH('Year-to-Date Summary'!C5,January!$C$5:$C$35,0)),0)+...

Hope this helps.

 

[eizyman]

This worked wonderful.

I am amazed at all the knowledge on this forum and those that are willing to go the extra mile for someone...thanks so much!!

 

[David20]

You're very welcome. Glad to help. I can't tell you how many times I've found answers on this board. I'm just happy to be on the helping side for a change!

 

[David20]

I haven't been able to get this formula out of my head. I hate to use a long, complex formula when something simple will do. I found a better solution in this thread.

Your formula (assuming you made column A "Last Name, First Name") would be:

=IF(A5="","",SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$12&"'!$C$5:$C$35"),A5,INDIRECT("'"&$H$1:$H$12&"'!$D$5:$D$35"))))

where H1:H12 is a list of your monthly sheet names, January to December, I assume. Adjust the range to exactly match the number of sheet names.