+ Reply to Thread
Results 1 to 14 of 14

Combine Vlookup with iferror and sums it up

  1. #1
    Registered User
    Join Date
    10-10-2014
    Location
    seattle wa
    MS-Off Ver
    2007
    Posts
    63

    Combine Vlookup with iferror and sums it up

    Hi,

    I need help with combining this statement. I need to lookup a item in worksheet and then sums up the total and if not item is found then leave it blank. I managed to built the statements with don't know how to combine it all together.

    =SUM(VLOOKUP(F7,'Accrual List'!$D:$K,8,0)) ==> this one sums up the total but if nothing found gives me #N/A

    =IFERROR(VLOOKUP(F2,'Accrual List'!$D:$K,8,FALSE),"") ==> this one leaves a blank if #N/A but doesn't help when it doesn't sum up.

    How do I combine these 2 things into one statement.

    Thank you

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    16,634

    Re: Combine Vlookup with iferror and sums it up

    Maybe like this:

    =SUM(IFERROR(VLOOKUP(F7,'Accrual List'!$D:$K,8,0),0)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  3. #3
    Valued Forum Contributor
    Join Date
    10-09-2017
    Location
    Ro
    MS-Off Ver
    2013
    Posts
    564

    Re: Combine Vlookup with iferror and sums it up

    Maybe this:
    =SUMIF(VLOOKUP(F7,'Accrual List'!$D:$K,8,0),VLOOKUP(F7,'Accrual List'!$D:$K,8,0),"<>",VLOOKUP(F7,'Accrual List'!$D:$K,8,0))

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,832

    Re: Combine Vlookup with iferror and sums it up

    Hi,
    I don't understand the use of SUM with VLOOKUP. A VLOOKUP returns a single value so what's SUM meant to do.

    If you have your data in a regular two dimensional table then personally I'd use a Pivot Table. Amongst other advantages its peforms the dual role of summing data for particular subsets.

    Upload the workbook and manually add the results you want if you need more definitive help.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Valued Forum Contributor
    Join Date
    10-09-2017
    Location
    Ro
    MS-Off Ver
    2013
    Posts
    564

    Re: Combine Vlookup with iferror and sums it up

    I think that you want this:

    =SUMIFS('Accrual List'!$K:$K,'Accrual List'!$D:$D, F7,'Accrual List'!$K:$K,"<>")

  6. #6
    Registered User
    Join Date
    10-10-2014
    Location
    seattle wa
    MS-Off Ver
    2007
    Posts
    63

    Re: Combine Vlookup with iferror and sums it up

    None of these formula's work. Reason I need to have sum is because there are more then 1 of those lookup in Accrual sheet. I need to add them together.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    16,634

    Re: Combine Vlookup with iferror and sums it up

    OK, let's start again!

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  8. #8
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    1,816

    Re: Combine Vlookup with iferror and sums it up

    So you are NOT combining 2 formula as you said in FP, but actually 3 or more?

    Anyway I think a SUMIFS as per Paul M's post will deliver success, but I don't understand your data well enough to suggest a specific syntax.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,832

    Re: Combine Vlookup with iferror and sums it up

    Quote Originally Posted by gill123 View Post
    None of these formula's work. Reason I need to have sum is because there are more then 1 of those lookup in Accrual sheet. I need to add them together.
    As I said in #4, upload the workbook. Anonymised if necessary.

  10. #10
    Valued Forum Contributor
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    962

    Re: Combine Vlookup with iferror and sums it up

    Lookups are text functions, SUM is a numeric function. Its oil and water 99% of the time.

    The reason being generally whatever criteria you are using to return a value from the lookup can just be criteria in a conditional SUM (SUMIF, SUMIFS, SUMPRODUCT, etc).

    A sample file and a description of what you want to accomplish (not what you have done that isnt working anyhow) would help us help you.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  11. #11
    Registered User
    Join Date
    10-10-2014
    Location
    seattle wa
    MS-Off Ver
    2007
    Posts
    63

    Re: Combine Vlookup with iferror and sums it up

    I have uploaded the worksheet
    Attached Files Attached Files

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,832

    Re: Combine Vlookup with iferror and sums it up

    As I said earlier,

    Use a pivot table and avoid the need for any formulae.
    See attached
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    962

    Re: Combine Vlookup with iferror and sums it up

    If I didnt misunderstand it seems like a simple SUMIF:

    C2 filled down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    10-10-2014
    Location
    seattle wa
    MS-Off Ver
    2007
    Posts
    63

    Re: Combine Vlookup with iferror and sums it up

    @Zer0Cool your formula works. Thank you everyone for helping me resolve this issue.

+ 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