Vlookup or Sumif

Rupert Bennett

Active Member
Joined
Nov 20, 2002
Messages
271
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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.
 

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
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)
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

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.
 

Rupert Bennett

Active Member
Joined
Nov 20, 2002
Messages
271
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
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

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:
 

cgreen

Active Member
Joined
Aug 14, 2002
Messages
291
Hi All, I'm trying to do almost the same thing and am having no luck playing with the formula. Hope you can help. :unsure:

=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??
 

cgreen

Active Member
Joined
Aug 14, 2002
Messages
291
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. :rolleyes:
 

Forum statistics

Threads
1,148,219
Messages
5,745,446
Members
423,952
Latest member
EduardoM

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top