# Vlookup or Sumif

#### Rupert Bennett

##### Active Member
Please help me with a formula, or VBA if necessary, to lookup a name in column"D" of a summary sheet, find that name in column "C" of a table on another page called "BenefitsUsed" and sum any data for that name in Column "E" of this sheet, if the date entered in column "D" of this sheet is for the current year. A name may be entered more than once on this sheet and the date entered in column "D" may be any year in the past.
A copy of a worksheet that is representative of the data on the BenefitsUsed sheet is attached. Thanks for the help you always give.
Rupert
Vacation Schedule2.xls
CDEF
7NamesDatesVacationHrsPersonalHrs
8Doe,john1/15/2005248
9Mouse,Mickey1/18/2005408
10Poppins,Mary1/8/2005328
11White,Snow7/15/2004408
12Doe,john8/2/2004168
13Mouse,Mickey3/16/2004248
14Poppins,Mary11/15/2004328
15White,Snow1/12/2005408
16Doe,john7/25/2003408
17Doe,john1/8/2005168
18Mouse,Mickey11/20/2003248
19Poppins,Mary10/24/2003408
20White,Snow10/23/2003408
21White,Snow1/3/2005408
22Poppins,Mary1/22/2005168
23Mouse,Mickey1/12/2005408
BenefitsUsed

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
If I understand,

=SUMPRODUCT(--(C8:C23=A1),--(YEAR(D3:D28)=2005),E3:E23)

Where A1 houses a name. Substitute the appropriate sheet name for the ranges.

Please help me with a formula, or VBA if necessary, to lookup a name in column"D" of a summary sheet, find that name in column "C" of a table on another page called "BenefitsUsed" and sum any data for that name in Column "E" of this sheet, if the date entered in column "D" of this sheet is for the current year. A name may be entered more than once on this sheet and the date entered in column "D" may be any year in the past.
A copy of a worksheet that is representative of the data on the BenefitsUsed sheet is attached. Thanks for the help you always give.

=SUMPRODUCT(--(BenefitsUsed!C\$2:C\$10='Summary Sheet'!D2),--(YEAR(BenefitsUsed!D\$2:D\$10)=YEAR(TODAY())),BenefitsUsed!E\$2:E\$10)

Hi,

Something like: ??
Book1
ABCD
1NamesSum
2Doe,john24
3Mouse,Mickey80
4Poppins,Mary48
5White,Snow40
Summary

Formula in is : =IF(MATCH(A2,BenefitsUsed!\$C\$8:\$C\$23,0),SUMPRODUCT(--(BenefitsUsed!\$C\$8:\$C\$23=A2),--(YEAR(BenefitsUsed!\$D\$8:\$D\$23)=YEAR(TODAY())),(BenefitsUsed!\$E\$8:\$E\$23)),0) copied down

Formula is in B2 copied down

Edit, PS : I typed in the wrong date for the first Snow White entry, had it down as 2004 so the formula is correct.

My thanks to Brian, Todd and Fergus for the assistance given. All solutions were along the same line and will work. I decided to go with Fergus' solution as this works perfectly for me and will work for future years without any correction to the formula ever again. I don't know if you realize how much help you give to people like me and how appreciated you all are. Thank you so much.
Rupert

Hi Rupert,

It's a pleasure, and I know what you mean. Nine months ago I could never have even attempted to answer your question, it's only by having learned from this Board that I could muster an answer. :wink:

Hi All, I'm trying to do almost the same thing and am having no luck playing with the formula. Hope you can help.

=IF(B5>0,IF(MATCH(\$A\$1,'[Copy of Document Register.xls]Production Control'!\$B\$2:\$B\$1001,0),SUMPRODUCT(--('[Copy of Document Register.xls]Production Control'!\$C\$2:\$C\$1001=B5),('[Copy of Document Register.xls]Production Control'!\$J\$2:\$J\$1001)),0))

The month I am working on is in A1. I want it to look at A1 then go to the 'copy of document register' and look at column B to see if it matches A1. If column B from 'copy of document register' matches A1, I want it to look at B5 and match to column C on the 'copy of document register' and total up the numbers in column J of the 'copy of document register'.

How do I paste a picture of my spreadsheet to be able to explain more??

Monthly Schedule.xls
ABCDEFGHIJ
1Jan-05
2ProgramsDatabaseDocumentRegister
3PCN'sforFY05NameTypeAuth.TotalsMonthSchdRequiredMonthSchdOrderedTotalOrdered
41N01JQ0T700EngOCMI05--
52N02JQ0T700EngRecapA3200#N/A76
63N01BCZT701CEngOCMI0101
74N02BCZT701CEngConvC250--
85N03BCZT701CEngRecapA322070
PC Count Report for FY05
Copy of Document Register.xls
BCDE
1"Orders" MonthofSchdPCNQTYOrderedQTYReturned
2Jan-05N021ZG4
3Jan-05N03ZA052
4Jan-05N02MF54
5Dec-04N01STH1
6Dec-04N01STK1
Production Control

Please refer to my statement above for what I am trying to achieve.

Replies
4
Views
275
Replies
6
Views
171
Replies
4
Views
127
Replies
2
Views
225
Replies
1
Views
104

### Forum statistics

1,221,052
Messages
6,157,632
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back