Vlookup or Sumif

[Rupert Bennett]

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
	C	D	E	F
7	Names	Dates	VacationHrs	PersonalHrs
8	Doe,john	1/15/2005	24	8
9	Mouse,Mickey	1/18/2005	40	8
10	Poppins,Mary	1/8/2005	32	8
11	White,Snow	7/15/2004	40	8
12	Doe,john	8/2/2004	16	8
13	Mouse,Mickey	3/16/2004	24	8
14	Poppins,Mary	11/15/2004	32	8
15	White,Snow	1/12/2005	40	8
16	Doe,john	7/25/2003	40	8
17	Doe,john	1/8/2005	16	8
18	Mouse,Mickey	11/20/2003	24	8
19	Poppins,Mary	10/24/2003	40	8
20	White,Snow	10/23/2003	40	8
21	White,Snow	1/3/2005	40	8
22	Poppins,Mary	1/22/2005	16	8
23	Mouse,Mickey	1/12/2005	40	8
BenefitsUsed

 

[Brian from Maui]

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]

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]

Hi,

Something like: ??

Book1
	A	B	C	D
1	Names	Sum
2	Doe,john	24
3	Mouse,Mickey	80
4	Poppins,Mary	48
5	White,Snow	40
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]

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]

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]

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]

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

 

[Fergus]

Hi,

Use Colo's HTML Maker here http://www.puremis.net/excel/downloads.shtml or follow link at the bottom of the page. Also read and print instructions from article at the top of the Forum page

 

[cgreen]

Monthly Schedule.xls
	A	B	C	D	E	F	G	H	I	J
1	Jan-05
2		ProgramsDatabase							DocumentRegister
3		PCN'sforFY05	Name	Type	Auth.Totals		MonthSchdRequired		MonthSchdOrdered	TotalOrdered
4	1	N01JQ0	T700Eng	OCMI0	5					--
5	2	N02JQ0	T700Eng	RecapA3	200				#N/A	76
6	3	N01BCZ	T701CEng	OCMI0	10					1
7	4	N02BCZ	T701CEng	ConvC2	50					--
8	5	N03BCZ	T701CEng	RecapA3	220					70
PC Count Report for FY05

Copy of Document Register.xls
	B	C	D	E
1	"Orders" MonthofSchd	PCN	QTYOrdered	QTYReturned
2	Jan-05	N021ZG	4
3	Jan-05	N03ZA0	5	2
4	Jan-05	N02MF5	4
5	Dec-04	N01STH	1
6	Dec-04	N01STK	1
Production Control

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