Re: vlookup addition
- From: "T. Valko" <biffinpitt@xxxxxxxxxxx>
- Date: Wed, 21 Jan 2009 12:47:37 -0500
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
"Pete_UK" <pashurst@xxxxxxxxxxx> wrote in message
news:7ca0fba2-e82d-4bea-8a68-19d9f7c1c8cb@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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,FALSE))+VLOOKUP("Logistics",$G$1:$H$205,2,FALSE)-
Hide quoted text -
- Show quoted text -
.
- References:
- vlookup addition
- From: nt
- Re: vlookup addition
- From: Pete_UK
- Re: vlookup addition
- From: nt
- Re: vlookup addition
- From: Pete_UK
- vlookup addition
- Prev by Date: finding n-th most frequently occuring word
- Next by Date: Re: Net should be zero but it is not; decimal values aroung the 12
- Previous by thread: Re: vlookup addition
- Next by thread: Re: vlookup addition
- Index(es):
Relevant Pages
|
