Adding multiple VLOOKUP totals together? [BASIC Q]

SideWinder

SideWinder

SideWinder

Consigliere
Joined
12 Jun 2004
Posts
151,030
Location
SW17
I have been googling but can't seem to find the 'basic' version. :o

I have 4 cells that produce a price using a VLOOKUP so B8 is the name of the product and B9 is the price.

This continues across with D8 being the product name and the price underneath, D9 etc.

In B12, I would like a formula that just adds B9+D9+E9+F9 but can't seem to find it.

Any ideas?

My table of product names is J5 down to J24 with the prices next to them so K5 and K24. :)
 
Thanks @t31os but it doesn't work because some cells have to be blank. ..comes up as NA.

Not all of the cells have numbers in so B9 does but D9, F9 and H9 don't.

How can I get round this?
 
t31os said:
Then you probably want SUMIF instead of SUM.
Code: 
=SUMIF(B9:F9,"<>")

I think SUM should still SUM a range even if some of the cells are empty/blank, i did only test on Google Sheets, but i think most of the functions work the same (it might not in Excel but i don't have it installed to check as i do all my spreadsheet stuff on Google these days).
Click to expand...

Ahhhh I think it works! Thank you! :D
 
t31os said:
Then you probably want SUMIF instead of SUM.
Code: 
=SUMIF(B9:F9,"<>")

I think SUM should still SUM a range even if some of the cells are empty/blank, i did only test on Google Sheets, but i think most of the functions work the same (it might not in Excel but i don't have it installed to check as i do all my spreadsheet stuff on Google these days).
Click to expand...

Hey mate!

Ok so I moved to Google Sheets and SUMIF just keeps the total in B12 as £0 so it doesn't add. I tried switching back to SUM and that had it as hashtag VALUE instead.

Any ideas? :)

@cheesyboy Apologies but I think I got confused...how would IFERROR work in my example, would I put it in the VLOOKUP cell but not the total one?
 
cheesyboy said:
From what I understood, a bog-standard SUM won't work because of the NA errors your VLOOKUP produces. Stick the IFERROR thing in all your VLOOKUP formulas in row 9 so that you get a zero instead of an error, so then a regular SUM will add it all up.
Click to expand...

Hmm so the 0 is showing ok and everything looks great but the issue is the cell not adding up.

@t31os - I have added a quick screenshot which I'll delete soon.

REMOVED.
 
Last edited:
@t31os Oh my I think I've got it...yes apologies I didn't pick up on what you said above. :)

I had the price list as £2 and currency so swapped those to 2 and then it picked it up!

Thank you so much! :)
 
You must log in or register to reply here.
Back
Top Bottom