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.
Sum Of Vlookups Returns N/a
-
-
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
-
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.
-
Re: Sum Of Vlookups Returns N/a
Quote from sriyaBIZ 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,
No special add-in at all.
1) Data->Pivot table & Pivot Chart Report
2) Select the check box "Multiple Consolidation Ranges"
3) Click "Next"
3) Click "Add"
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
Quote from sriyaThanks 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!