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. :)
 
A SUM call in B12 will add up your B9 to F9 range.

Code: 
=SUM(B9:F9)

If you want to specifically skip the C column, then.

Code: 
=SUM(B9,D9:F9)
 
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?
 
SideWinder said:
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?
Click to expand...

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).
 
Last edited:
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?
 
SideWinder said:
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?
Click to expand...

SUM needs numeric values to work with across a range, if you're storing prices with a literal £ it will treat them as text.

You need to have your price cells only contain the value, eg. for £20 your cell should store 20 or 20.00 and then have those cells set to a currency format(so visually it's £20 or £20.00) which is treated as a numeric value, which SUM/SUMIF can then work with.

If you're still stuck, feel free to link a google sheet with some example data and i'll have a look.
 
Last edited:
SideWinder said:
@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?
Click to expand...
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.
 
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:
Did you pick up what i said above about the value in cells and the format applied to those cells?

I cannot replicate the issue, i used the same lookup and sum call, but naturally just lobbed in some random numbers for the sake of testing.

[REDACTED] ;)

I don't need to actually see your real data, but if you make a sample sheet with a small sample of data i can look at, i may be able to spot the issue. EDIT: Or just edit mine below if you like.

Here's my test sheet.
[REDACTED] ;)
 
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