vlookup addition

[nt]

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)

 

[Pete_UK]

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

 

[nt]

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]

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

 

[T. Valko]

If you have up to 15 variables it'd be better to list those variables in a
range of cells then use a formula like this:

=SUMPRODUCT(--(ISNUMBER(MATCH(G1:G100,A1:A15,0))),H1:H100)

Where A1:A15 are the list of variables.

--
Biff
Microsoft Excel MVP


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

 

[nt]

I must be doing something wrong - the vlookup works perfectly but the
sumproduct is returning the #value! error - also, remember that not all of
the categories will appear every month (isna*). I love the sumproduct idea -
what am I doing wrong and can it calculate a sum if one of the items is not
there?

 

[Pete_UK]

What both SP formulae are saying is if the cells in column G are equal
to Accounting OR Sales OR Logistics (etc) then add the corresponding
cell from column H. The main difference between my suggestion and
Biff's is that in mine the variables are listed within the formula
(and so it would need to be amended if you wanted to change some
values), whereas Biff's solution uses a table to list the variables,
and so you only need to amend the table rather than the formula -
however, they are functionally the same.

If you are getting a #VALUE error then you might have arrays which are
of different lengths (they should be the same size), or your data may
be text rather than numbers.

Post the exact formula(s) that you used, so that we might spot your
error more specifically.

Hope this helps.

Pete