Using VLOOKUP to sum/average/combine multiple results

[ossuary]

Greetings, fellow Excel users. I'm hoping one of you kind people can give me a hand with what I assume is a relatively simple problem, but one I haven't yet been able to crack on my own. I'm trying to use a VLOOKUP function to populate paid time off hours for my employees into a spreadsheet based on reports that I feed into Excel. The problem I am having is when there is more than one result per employee ID. I need a way to get Excel to add up the number of hours when there is more than one entry per employee ID.

For reference, this is the function I am currently using. "PTO" is the name I have defined for the section of the spreadsheet that I dump the report into.

=IF(ISNA(VLOOKUP(A35,PTO,3,FALSE)),"",VLOOKUP(A35,PTO,3,FALSE))

This function does exactly what I want it to do if there are 0 or 1 result per employee ID, but doesn't do anything with additional entries for the same employee. Any help you guys could provide on this would be GREATLY appreciated.

I have used the HTML maker suggested on your forum to create an example of the data I am using. Hopefully this will help:

Example.xls
	A	B	C	D
1	EmpID	Date	Hours
2	102	AUG-01-2004	8.00
3	102	AUG-05-2004	4.50
4	99	AUG-06-2004	8.00
5	228	JUL-28-2004	8.00
6	99	JUL-30-2004	8.00
7	102	JUL-30-2004	8.00
8	357	JUL-30-2004	8.00
9	456	JUL-30-2004	8.00
10
PTO

Thanks in advance!!

 

[Nimrod]

Use the SumIf function :wink:

example of totaling all hours for empl# 102

=SUMIF(A:A,102,C:C)

In other words ... look in Column A , for value 102, when found total corresponding values in Column C

 

[ossuary]

Ah, thank you for the advice Nimrod, that is exactly the sort of simple answer I needed. I KNEW there had to be a function in there somewhere that already did what I was looking for, I just couldn't find it. I have a followup question though.

How do I combine that function with the function I am already using? I need to use it inside a VLOOKUP, because a) the function is on a different sheet than the paid time off report output, and b) the employee numbers are not static... I have it set up so you type in an employee's number, and it pulls up all the relevant data in subsequent boxes using VLOOKUPs. Any ideas on how I can get the syntax to work correctly?

Thanks again for the help!

 

[Aladin Akyurek]

I don't think you need VLOOKUP() at all.

=SUMIF(INDEX(PTO,0,1),A35,INDEX(PTO,0,3))

should suffice.

 

[ossuary]

*does a happy dance*

It works, it works! Thank you!

ray:

 

[Rayzzor]

Hey...

I was reading this read in search of a way to get a percentage of a column that has vlookups. If I reference a column that does not have the vlookups... the formula works... if I reference the column that has the vlookup... I get a #n/a

This is what the formula I am working with looks like in the above scenario:

=IF(ISERROR(SUM(IF('Raw Data - CSAT'!$AR$4:$AR$4000=$A9,IF('Raw Data - CSAT'!$N$4:$N$4000>"3",1,0)))/$I9),"",(SUM(IF('Raw Data - CSAT'!$AR$4:$AR$4000=$A9,IF('Raw Data - CSAT'!$N$4:$N$4000>"3",1,0)))/$I9))

Any Ideas??

 

[Aladin Akyurek]

Hey...

I was reading this read in search of a way to get a percentage of a column that has vlookups. If I reference a column that does not have the vlookups... the formula works... if I reference the column that has the vlookup... I get a #n/a

This is what the formula I am working with looks like in the above scenario:

=IF(ISERROR(SUM(IF('Raw Data - CSAT'!$AR$4:$AR$4000=$A9,IF('Raw Data - CSAT'!$N$4:$N$4000>"3",1,0)))/$I9),"",(SUM(IF('Raw Data - CSAT'!$AR$4:$AR$4000=$A9,IF('Raw Data - CSAT'!$N$4:$N$4000>"3",1,0)))/$I9))

Any Ideas??

You should have started your own thread, possibly with a reference to the current one...

If you're not going to control what VLOOKUP formulas return, then try:

=SUM(IF(1-ISNA('Raw Data - CSAT'!$AR$4:$AR$4000),IF('Raw Data - CSAT'!$AR$4:$AR$4000=$A9,IF(ISNUMBER('Raw Data - CSAT'!$N$4:$N$4000),IF('Raw Data - CSAT'!$N$4:$N$4000>3,1)))))

which you need to confirm with control+shift+enter instead of just with enter.

 

[Rayzzor]

Hey...

I was reading this read in search of a way to get a percentage of a column that has vlookups. If I reference a column that does not have the vlookups... the formula works... if I reference the column that has the vlookup... I get a #n/a

This is what the formula I am working with looks like in the above scenario:

=IF(ISERROR(SUM(IF('Raw Data - CSAT'!$AR$4:$AR$4000=$A9,IF('Raw Data - CSAT'!$N$4:$N$4000>"3",1,0)))/$I9),"",(SUM(IF('Raw Data - CSAT'!$AR$4:$AR$4000=$A9,IF('Raw Data - CSAT'!$N$4:$N$4000>"3",1,0)))/$I9))

Any Ideas??

You should have started your own thread, possibly with a reference to the current one...

If you're not going to control what VLOOKUP formulas return, then try:

=SUM(IF(1-ISNA('Raw Data - CSAT'!$AR$4:$AR$4000),IF('Raw Data - CSAT'!$AR$4:$AR$4000=$A9,IF(ISNUMBER('Raw Data - CSAT'!$N$4:$N$4000),IF('Raw Data - CSAT'!$N$4:$N$4000>3,1)))))

which you need to confirm with control+shift+enter instead of just with enter.

Sorry about not starting a new thread... won't happen again.

But anyways... thank you very much, after tweaking the code a bit (forgot to mention that the vlookup column results was text) I was able to make it work!!

Cheers!!!