+ Reply to Thread
Results 1 to 12 of 12

summing VLOOKUP values

  1. #1
    Registered User
    Join Date
    10-09-2012
    Location
    florida
    MS-Off Ver
    Excel 2003
    Posts
    11

    summing VLOOKUP values

    I am using SUM as a formula to add different vlookup values in different sheets.

    The formula works unless vlookup does not find the lookup value in the table array; in which case I get an "#NA" error.

    How do I get a vlookup to ignore the value if one of the vlookup values is missing?


    =SUM(VLOOKUP(B6,Monday!$1:$1048576,2,FALSE),VLOOKUP(B6,Tuesday!$1:$1048576,2,FALSE), VLOOKUP(B6,Wednesday!$1:$1048576,2,FALSE),VLOOKUP(B6,Thursday!$1:$1048576,2,FALSE),VLOOKUP(B6,Friday!$1:$1048576,2,FALSE))


    Thanks in advance.

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: summing VLOOKUP values

    Try this:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Registered User
    Join Date
    10-09-2012
    Location
    florida
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: summing VLOOKUP values

    Worked like a charm.

    Thanks!

    However, I am now trying to use the same logic for "average" instead of "sum".

    Should I start a new thread for that?

    My original formula was the below, but I am sure it is wrong. Again, it works when it finds all the lookup values. I tried chaning your suggestion of countif for averageif but it really did not work, it gave me really high numbers.


    =AVERAGE(VLOOKUP(B8,Monday!$1:$1048576,3,FALSE),VLOOKUP(B8,Tuesday!$1:$1048576,3,FALSE), VLOOKUP(B8,Wednesday!$1:$1048576,3,FALSE),VLOOKUP(B8,Thursday!$1:$1048576,3,FALSE),VLOOKUP(B8,Friday!$1:$1048576,3,FALSE))

  4. #4
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: summing VLOOKUP values

    If one of your lookups does not find an answer, do you want the average to consider it a zero, or not count it at all?

    E.g.
    3,0,4,0,2 Average is 1.8
    3, ,4, ,2 Average is 3

  5. #5
    Registered User
    Join Date
    10-09-2012
    Location
    florida
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: summing VLOOKUP values

    I guess not count it at all, please.


    BTW, your help is so wonderful!!! I definitely appreciate it

  6. #6
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: summing VLOOKUP values

    Try this:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-09-2012
    Location
    florida
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: summing VLOOKUP values

    I tried your suggestion, unfortunately it is not giving me an average.


    I am using time averages, though. I am unsure if that makes any difference.


    B6 on monday had a talk time of 3:35
    Tuesday: 2:52
    Wednesday: 2:59
    Thursday: 2:09
    Friday: 2:07

    After I enter the suggested formula, it says that the average talk time for the week is 14:24

  8. #8
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: summing VLOOKUP values

    14:24 or 14:44?

  9. #9
    Registered User
    Join Date
    10-09-2012
    Location
    florida
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: summing VLOOKUP values

    It is 14:24

  10. #10
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: summing VLOOKUP values

    Sorry, out of ideas on that one.

  11. #11
    Registered User
    Join Date
    10-09-2012
    Location
    florida
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: summing VLOOKUP values

    sample average vlookup.xlsx

    Would it help if I attach a sample file?

  12. #12
    Registered User
    Join Date
    10-09-2012
    Location
    florida
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: summing VLOOKUP values

    My mistake. I have now realized that I was referencing the wrong dump in the vlookup section. I should have asked to give me column 3 instead of 2. I am now using:

    =AVERAGE(IF(COUNTIF(Monday!$1:$1048576,B6),VLOOKUP(B6,Monday!$1:$1048576,3,FALSE),""),IF(COUNTIF(Tuesday!$1:$1048576,B6),VLOOKUP(B6,Tuesday!$1:$1048576,3,FALSE),""),IF(COUNTIF(Wednesday!$1:$1048576,B6),VLOOKUP(B6,Wednesday!$1:$1048576,3,FALSE),""),IF(COUNTIF(Thursday!$1:$1048576,B6),VLOOKUP(B6,Thursday!$1:$1048576,3,FALSE),""),IF(COUNTIF(Friday!$1:$1048576,B6),VLOOKUP(B6,Friday!$1:$1048576,3,FALSE),""))


    The above gives me the right average, but only on the days where an agent does not miss a day. Do you have any feedback?

    I am getting a "#Value" error.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1