SUMPRODUCT for all cells with VLOOKUP or IF or SUMIFS

Hello,

I need your help to come up with a one-cell formula to calculate a sumproduct for all the rows. In sheet 1, I have names in col#1 and hours in col#2. In sheet 2, I have same names in col#1 but not in the same order as sheet 1 and col#2 has rate/amount. I can create one more column col#3 in sheet 1 to get "rate/amount" from sheet 2 using vlookups. Then do sumprocut at the bottom for grand total. What I want to do if possible is nest both vlookup and sumproduct in one-cell instead of cell by cell.

Sheet 1
Name Hours
ABCD 16
DEF 30
GHI 10
HKL 40

Sheet 2
Name Rate
HKL 100
DEF 80
GHI 80
ABCD 60

One possible way of doing is
Sheet 1
Name Hours Sumproduct vlookup for each cell
ABCD 16 sumproduct(vlookup(abcd,sheet2,2,0)*hours = 60*16

Then add the total of each cell.

Hope I did not make it appear to complicated. What I am hoping to do is nest all of these in once cell to calculate grand total (without creating a new column for lookup or sumproduct) - i.e., perform sumproduct for all rows by looking up at respective values from another table and show it in one cell.

Please let me know if you need more information. Thanks in advance for your time and help.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

are you sure you don't want a sumif or sumifs for that? A vlookup will only find the first instance of an item and return that. A sumif will return the sum of all instances of something. Or maybe you simply want a vlookup and at the end the multiplication of the value returned to get the hours? A sample sheet might be a help, one with representative samples of your data AND expected results. Go advanced to attach a sheet.

Originally Posted by Sambo kid

are you sure you don't want a sumif or sumifs for that? .

Thank you, Sambo, for your reply. I can do SUMIF or SUMIFS if I create a new column for each row/name. I would like to get the grand total of sumproduct in one cell. I have attached a sample. Hope that helps.

Originally Posted by Glenn Kennedy

Will you please attach a SMALL sample Excel workbook ...

Thank you, Glen. I have attached a sample file.

Macro or UDF will do?

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Pl see file.
ARRAY Formula. Pl see note below.

Please Login or Register  to view this content.

Thanks kvsrinivasamurthy. Appreicate your help. It took some time to undesrstand the formula as I never used MMULT. This is very helpful.

Does both the tables vary or any one.

Pl upload a sample file

Revised formula so that Rows can be changed in both Sheets

Please Login or Register  to view this content.