Vlookup with Sum, multiple sheets SOLUTION FOUND THANKS TO ALL!

. . . I have put together this formula, however because the field that the formula is looking for in the table array doesn't exist in all the sheets (all the table arrays) it calculated #N/A.

How do I get it to totals and egnor those it cannot find.

=SUM(VLOOKUP($A8,'Smiths Street'!A:M,2,FALSE),VLOOKUP($A8,'Chanel Road'!A:M,2,FALSE) etc. )

Thanks

Excel 2016

Hi,

Instead of VLOOKUP, You may use SUMIF.

I tried to recreate Your situation as follows:

- I created 3 worksheets: Smiths Street, Chanel Road and Sheet1.

- In worksheets Smiths Street and Chanel Road, I input dummy data in columns A and B.

-- I chose column A because Your lookup_value is in column A.

-- I chose column B because Your col_index_num is 2.

• In 3rd worksheet >> in cell B8, formula is: =SUMIF('Smiths Street'!$A$1:$A$10,A8,'Smiths Street'!$B$1:$B$10)+SUMIF('Chanel Road'!$A$1:$A$10,A8,'Chanel Road'!$B$1:$B$10)

- I dragged the formula down to more rows.

Note:

i. In worksheet Smiths Street >> ABC is missing.

ii. In worksheet Chanel Road >> EFG is missing.

The formula still got the correct results.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Hi

Your answer

=SUMPRODUCT(SUMIF(INDIRECT($K$4:$K$11&"!A2:A100"),$A8,INDIRECT($K$4:$K$11&"!B2:B100")))

K4:K11 on the Summary sheet has the worksheet names.

I don't understand how my table arrays fit into the formula

1. I just list the sheets on the Summary sheet? Like a new sheet column A rather than K.
2. A2:A100 is the table array of each sheet?

3)Why Indirect A2:A100 and then Indirect B2:B100 Is this just the 2nd table array?

Much appreciated

Hi there

Try by

1. Making columns A and M absolut
2. Returning 0 zero if the VLOOKUP formula found nothing i.e. #N/A

Try the formula

=SUM(IFERROR(VLOOKUP($A8,'Smiths Street'!$A:$M,2,FALSE),0),IFERROR (VLOOKUP($A8,'Chanel Road'!$A:$M,2,FALSE),0))

If the answer doesn't work please provide a link to a copy of your file so w could fix it on our side.

Regards

Jeovany

Hi

Thanks so much. The result is 0.

I have 9 Sheets to total.

Columns A in the consolidated is all the ones to look up

Current year earnings

Retained earnaings

Foreign Currency Revaluation Reserve

New Share Capital

etc.

Sheet 1 has Current Earning $ figure and Retained Earnings $ figure but nothing for the others

Sheet 9 has Current Earning $ figure and Retained Earnings $ figure, New Share Capital $ figure

etc

Hope this helps!

Hi Thanks. I can't send you the file for confidential reasons. I tried it and it says too many arguments.

Thanks