Adding Multiple Results Using Vlookup

[hawong]

I am using Vlookup to search for staff members who have worked overtime, and return the hours worked in a summary sheet. The excel sheet is divided into months and I have created a summary sheet which has the Vlookup formula which is set up to search in the various months for the staff name and record the hours work in the summary sheet. My problem is:

Sometimes the staff name occurs more than once e.g. Andre Stevens occurs 3x in March. However Vlookup only returns one of the results. How do I get it to add all the results and return the sum in the Summary column?

[Pete_UK]

You could use SUMPRODUCT for this, as this allows you to sum using multiple conditions (i.e. name = "x", month = March etc). Attach a sample workbook (the FAQ describes how) so that we can see how it would fit in to your existing data.

Hope this helps.

Pete

[hawong]

Yes I tried using the sum formula with the Vlookup but it didn't work for me. My current formula as it stands is:

=VLOOKUP("Andre Stevens",March!A8:H52,8)

How would I add in the SUM formulae to this?

[oeldere]

Probaly with a pivot table.

You get better help, if you post an example.

[Pete_UK]

It's SUMPRODUCT, not SUM. You will need to tell me which column contains the names, which column contains the dates, which column contains the hours etc. It would be easier for you to attach a sample workbook, as I requested.

Pete

[hawong]

Yes I tried using the sum formula with the Vlookup but it didn't work for me. My current formula as it stands is:

=VLOOKUP("Andre Stevens",March!A8:H52,8)

How would I add in the SUM formula to this?

[hawong]

Hello, any insights?

[oeldere]

See worksheet total and pivot table.

The pivot table is based on the worksheet total.

[hawong]

Hi

The pivot table does the trick without using the formula. Thanks. R u saying that the Vlookup cannot be manipulated to give one the same results.

[Pete_UK]

You can put this formula in C9 of your summary sheet:

=SUMPRODUCT(--(INDIRECT(C$8&"!A8:A100")=$A9),INDIRECT(C$8&"!H8:H100"))

Then copy it down and across as required. Note: don't copy it beyond July (column I) until you add sheets for those later months - you will get #REF errors if you do.

Hope this helps.

Pete

[oeldere]

Vlookup won't work for you.

Also an example with sumproduct (see month may and june).

In the past I used indirect to get to month in the formula.

I'm advised not to use it, because it makes the workbook slow, if it gets many calculations.

Do you have questions, on this item, just ask.

[hawong]

It worked! Can you explain what it is doing? I would really appreciate it.

[oeldere]

g9

Please Login or Register  to view this content.

See in sheet May in the range A8:A100

See if you find the value posted in A9 (Andre Stevens)

If it finds Andre stevens in the range A8:A100

Count all values (responding to Andre Stevens) in the range H8:H100

Please reply if I explained it well enough.

[hawong]

g9

Please Login or Register  to view this content.

See in sheet May in the range A8:A100

See if you find the value posted in A9 (Andre Stevens)

If it finds Andre stevens in the range A8:A100

Count all values (responding to Andre Stevens) in the range H8:H100

Please reply if I explained it well enough.

Yes I believe I get you. I am going to add some more names and try the SUMPRODUCT formula. Thanks again to you and Peter.

[oeldere]

Glad I could help. Thanks for the reply.

If the question is solved, will you mark your question as solved?

If you have (other) questions, just ask.

[hawong]

Thanks Pete and Oledere. Is this how you mark it as solved?

[oeldere]

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save