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

 

[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 (D-E) of the 'copy of document register'.

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

 

[Aladin Akyurek]

...

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

See:

http://www.mrexcel.com/board2/viewtopic.php?t=92622

Also: Start your own thread.

 

[cgreen]

Sorry, I didn't mean to quote the question about bringing my spreadsheet over. It is attached above ... thanks for the info

 

[Fergus]

Hi,

Is this what you want to do? I had to change some of the PCN references as in your example none were the same on both sheets so there was no match!!!

Here's my first sheet:

Copy of Document Register.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					0
5	2	N02JQ0	T700Eng	RecapA3	200					4
6	3	N01BCZ	T701CEng	OCMI0	10					0
7	4	N02BCZ	T701CEng	ConvC2	50					3
8	5	N01STK	T701CEng	RecapA3	220					0
PC Count Report for FY05

Formula in J4 copied down is : =SUMPRODUCT(--('Production Control'!$B$2:$B$6=$A$1),(--('Production Control'!$C$2:$C$6=B4)),('Production Control'!$D$2:$D$6))-SUMPRODUCT(--('Production Control'!$B$2:$B$6=$A$1),(--('Production Control'!$C$2:$C$6=B4)),('Production Control'!$E$2:$E$6))

It would be much better if you could combine your columns D & E to give a net total as that would halve the length of the formula, unless Aladin gives you a better one anyway. HTH

 

[Fergus]

Here's my second sheet so you can see which PCN references I changed:

Copy of Document Register.xls
	B	C	D	E
1	"Orders" MonthofSchedule	PCN	QTYOrdered	QtyReturned
2	Jan-05	N02JQ0	4
3	Jan-05	N02BCZ	5	2
4	Jan-05	N02MF5	4
5	Dec-04	N01STH	1
6	Dec-04	N01STK	1
Production Control

 

[cgreen]

Fergus, this works GREAT!!!! thank you, thank you, thank you

 

[Aladin Akyurek]

Hi,

Is this what you want to do? I had to change some of the PCN references as in your example none were the same on both sheets so there was no match!!!

...

Formula in J4 copied down is : =SUMPRODUCT(--('Production Control'!$B$2:$B$6=$A$1),(--('Production Control'!$C$2:$C$6=B4)),('Production Control'!$D$2:$D$6))-SUMPRODUCT(--('Production Control'!$B$2:$B$6=$A$1),(--('Production Control'!$C$2:$C$6=B4)),('Production Control'!$E$2:$E$6))

It would be much better if you could combine your columns D & E to give a net total as that would halve the length of the formula, unless Aladin gives you a better one anyway. HTH

You can make of this a single SumProduct formula...

=SUMPRODUCT(--('Production Control'!$B$2:$B$6=$A$1),--('Production Control'!$C$2:$C$6=B4),('Production Control'!$D$2:$D$6-'Production Control'!$E$2:$E$6))

 

[Fergus]

Thanks Aladin,

I was sure you'd improve it if you saw it. We all learn something from you every day.