# Sum Of Vlookups Returns N/a

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• I am trying to add up vlookups on one page. I have got one summary page on which I am trying to add up (merge) data across 3 sheets based on the criteria on the summary page. Because one of the data sheets may not have any data, the overall sum formula returns N/A. How do I solve this? It seems a very simple task but I am stuck. I have searched through the forum and came across a sumproduct formula but even that doesnt work. Please help. Attaching workbook.

## Files

• Re: Sum Of Vlookups Returns N/a

Hi,

Have look at attachment? I have used Pivot tables with mulitple ranges for consolidation.

This is the easiest method I can think of.

Biz

## Files

• Re: Sum Of Vlookups Returns N/a

Or try multiple SUMIF formulae

Richard

Edit: Like this

=SUMIF('Usage Charges'!\$A\$2:B\$56,Summary!\$A2,'Usage Charges'!B\$2:B\$56)+SUMIF('Service and equip'!\$A\$2:B\$56,Summary!\$A2,'Service and equip'!B\$2:B\$56)+SUMIF('Other Charges Credits'!\$A\$2:B\$14,Summary!\$A2,'Other Charges Credits'!\$B\$2:B\$14)

and copy down. Note mixed absolute and relative references.

If absence makes the heart grow fonder, and familiarity breeds contempt, perhaps my wife should live in Darwin?

• Re: Sum Of Vlookups Returns N/a

BIZ That worked great. Can you pleeease tell me how you did it? I tried to re do it in pivot table but the wizard doesnt let me add more than one range. Do you have a special add-in on your excel program?

• Re: Sum Of Vlookups Returns N/a

Quote from sriya

BIZ That worked great. Can you pleeease tell me how you did it? I tried to re do it in pivot table but the wizard doesnt let me add more than one range. Do you have a special add-in on your excel program?

Hi Sriya,

1) Data->Pivot table & Pivot Chart Report
2) Select the check box "Multiple Consolidation Ranges"
3) Click "Next"
4) Repeat steps 3 for all other worksheet tabs.
5) Click "Finish"
6) Double Click on cell A3 "Count of Value" and then select "Sum"

Let me know how you go.

Biz: D
:cheers:

• Re: Sum Of Vlookups Returns N/a

Thanks Biz, it did work this time. Thanks very much.
Richards thanks for your help too. I am in the middle of trying it out.

• Re: Sum Of Vlookups Returns N/a

Quote from sriya

Thanks Biz, it did work this time. Thanks very much.
Richards thanks for your help too. I am in the middle of trying it out.

No problems pleasure is mine.

Biz
:cheers:

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!