Re: vlookup addition

From: nt <nt@xxxxxxxxxxxxxxxxxxxxxxxxx>
Date: Wed, 21 Jan 2009 08:45:01 -0800

Works perfectly! Thank you so much - you are a lifesaver! One question: do
you see a problem with this formula if I had to nest or include 15 different
look ups?

"Pete_UK" wrote:

You could do it like this:

=IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0,VLOOKUP("Accounting",
$G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Sales",$G$1:$H$205,2,0)),0,(VLOOKUP
("Sales",$G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Logistics",$G$1:$H
$205,2,0)),0,VLOOKUP("Logistics",$G$1:$H$205,2,0))

All one formula, so be wary of spurious line-breaks in the newsgroups.
It might be easier to see what is happening if I post it like this:

=IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0,
VLOOKUP("Accounting",$G$1:$H$205,2,0))
+IF(ISNA(VLOOKUP("Sales",$G$1:$H$205,2,0)),0,
VLOOKUP("Sales",$G$1:$H$205,2,0))
+IF(ISNA(VLOOKUP("Logistics",$G$1:$H$205,2,0)),0,
VLOOKUP("Logistics",$G$1:$H$205,2,0))

i.e. return zero instead of an error for each of the categories.

Hope this helps.

Pete

On Jan 21, 2:47 pm, nt <n...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I am using the following to add together the two categories. How can I
change it to still give me a sum even if one of the categories is missing?
Example: The June report shows a category of Sales & Logistics but doesn't
even mention Accounting. Using this formula gives me an error although I
still need to have an amount even if the Accounting category is missing. Is
this possible? Thanks in advance!!!

=VLOOKUP("Accounting",$G$1:$H$205,2,FALSE)+VLOOKUP("Sales",$G$1:$H$205,2,FALSE))+VLOOKUP("Logistics",$G$1:$H$205,2,FALSE)

Follow-Ups:
- Re: vlookup addition
  - From: Pete_UK

References:
- vlookup addition
  - From: nt
- Re: vlookup addition
  - From: Pete_UK

Prev by Date: Sum a column correctly after more info is added by sorting
Next by Date: RE: Present value of an annuity
Previous by thread: Re: vlookup addition
Next by thread: Re: vlookup addition
Index(es):
- Date
- Thread

Relevant Pages

Re: why dont you two poofters....
... You prompt nearly if Georgette's shape isn't missing. ... special legal offences will loosely should the cups. ... Are you horizontal, I mean, accounting in search of negative ... chance will point out of the helpful federation. ...
(rec.heraldry)
Re: vlookup addition
... range of cells then use a formula like this: ... look ups? ... still need to have an amount even if the Accounting category is ... missing. ...
(microsoft.public.excel.worksheet.functions)
Re: changing timeline to account for different types of accounting months?
... John's approach, unless I'm missing something, is to dump the data into ... Project for accounting purposes I'm having a conceptual problem with ... ... > We have to move any uncompleted work for that week into ...
(microsoft.public.project)
Re: vlookup addition
... As for your follow-on question, you might find it easier to use ... Then you could add further categories inside the curly braces, ... still need to have an amount even if the Accounting category is missing. ...
(microsoft.public.excel.worksheet.functions)
OLTL- Friday July 14 Epi
... I ended up missing the whole thing today and will miss the nest 2 weeks. ... opposite direction. ... Albert Einstein ...
(rec.arts.tv.soaps.abc)