Re: vlookup addition

Tech-Archive recommends: Speed Up your PC by fixing your registry

You're welcome.

As for your follow-on question, you might find it easier to use
something like this:

=SUMPRODUCT(--($G$1:$G$205={"Accounting","Sales","Logistics"}),$H$1:$H
$205)

Then you could add further categories inside the curly braces,
separated by a comma, as shown with the three already there.

Hope this helps.

Pete

On Jan 21, 4:45 pm, nt <n...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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,FA­­LSE))+VLOOKUP("Logistics",$G$1:$H$205,2,FALSE)- Hide quoted text -

- Show quoted text -

.

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
    ... Thank you so much - you are a lifesaver! ... you see a problem with this formula if I had to nest or include 15 different ... still need to have an amount even if the Accounting category is missing. ...
    (microsoft.public.excel.worksheet.functions)