# Combine Vlookup with iferror and sums it up

1. ## 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. ## Re: Combine Vlookup with iferror and sums it up

Maybe like this:

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

3. ## 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. ## 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.

5. ## 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. ## 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. ## 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. ## 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.

9. ## Re: Combine Vlookup with iferror and sums it up

Originally Posted by gill123
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. ## 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.

12. ## 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

13. ## Re: Combine Vlookup with iferror and sums it up

If I didnt misunderstand it seems like a simple SUMIF:

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

see attached

14. ## Re: Combine Vlookup with iferror and sums it up

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

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

#### 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