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.

    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,


    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


    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!

Register Yourself Login