VLOOKUP and SUM

**mldover89** · 01-29-2010, 07:42 PM

I'm trying to do a VLOOKUP equation that adds the results it finds from 5 different worksheets. It's to find a person's last name, then add the number of calls answered from each time their name is found.

I have this so far, and am not sure if any of it is correct.

=SUM(VLOOKUP("Monday!Hunnicutt, Tuesday!Hunnicutt, Wednesday!Hunnicutt, Thursday!Hunnicutt, Friday!Hunnicutt","Monday!$A$4:$C$60, Tuesday!$A$4:$C$60, Wednesday!$A$4:$C$60, Thursday!$A$4:$C$60, Friday!$A$4:$C$60",3))

The name of the sheets is the day of the week (Monday, Tuesday, etc)
It's searching for the last name (Hunnicutt)
The information is in A4:C60
And the numbers it needs to add are in column 3.

Any help would be greatly appreciated!

**stanleydgromjr** · 01-29-2010, 08:01 PM

mldover89,

Welcome to the Excel Forum.

Please post your workbook.

Click on "New Post" - then scroll down and see "Manage Attachments".

**mldover89** · 01-29-2010, 08:03 PM

Here's my Workbook

**stanleydgromjr** · 01-30-2010, 12:38 AM

mldover89,

See the attached workbook "Sumproduct - Monday thru Friday sheets - mldover89 - SDG11.xlsx".

The formula in sheet "Summary", cell B23 (copied down):
=SUMPRODUCT(--(Monday!$A$4:$A$60=RIGHT($A23,LEN($A23)-FIND(" ",$A23,1))),Monday!$C$4:$C$60)+SUMPRODUCT(--(Tuesday!$A$4:$A$60=RIGHT($A23,LEN($A23)-FIND(" ",$A23,1))),Tuesday!$C$4:$C$60)+SUMPRODUCT(--(Wednesday!$A$4:$A$60=RIGHT($A23,LEN($A23)-FIND(" ",$A23,1))),Wednesday!$C$4:$C$60)+SUMPRODUCT(--(Thursday!$A$4:$A$60=RIGHT($A23,LEN($A23)-FIND(" ",$A23,1))),Thursday!$C$4:$C$60)+SUMPRODUCT(--(Friday!$A$4:$A$60=RIGHT($A23,LEN($A23)-FIND(" ",$A23,1))),Friday!$C$4:$C$60)

**DonkeyOte** · 01-30-2010, 05:40 AM

For a good overview of the options available to you regards 3D Conditional Summation see John McGimpsey's page: http://www.mcgimpsey.com/excel/threedsumif.html

Without adapting your file you could as outlined already use 5 functions given the number of sheets if fixed - or you could use 1 singular volatile SUMPRODUCT.

If you opt not to add a concatenated name field to your daily sheets (ie first & last name so as to mimic your summary sheet field) then I would suggest given your use of XL2008 you use SUMIFS rather than SUMPRODUCT given the former is more efficient, eg:

Code:

Summary!B23
=SUMIFS(Monday!C$2:C$60,Monday!$B$2:$B$60,LEFT($A23,FIND(" ",$A23)-1),Monday!$A$2:$A$60,REPLACE($A23,1,FIND(" ",$A23&" "),""))+SUMIFS(Tuesday!C$2:C$60,Tuesday!$B$2:$B$60,LEFT($A23,FIND(" ",$A23)-1),Tuesday!$A$2:$A$60,REPLACE($A23,1,FIND(" ",$A23&" "),""))+SUMIFS(Wednesday!C$2:C$60,Wednesday!$B$2:$B$60,LEFT($A23,FIND(" ",$A23)-1),Wednesday!$A$2:$A$60,REPLACE($A23,1,FIND(" ",$A23&" "),""))+SUMIFS(Thursday!C$2:C$60,Thursday!$B$2:$B$60,LEFT($A23,FIND(" ",$A23)-1),Thursday!$A$2:$A$60,REPLACE($A23,1,FIND(" ",$A23&" "),""))+SUMIFS(Friday!C$2:C$60,Friday!$B$2:$B$60,LEFT($A23,FIND(" ",$A23)-1),Friday!$A$2:$A$60,REPLACE($A23,1,FIND(" ",$A23&" "),""))
applied to matrix B23:G67

If you concatenate the names on Mon-Fri you can use SUMIF which is obviously even more efficient than the above (ie one condition rather than two)