Re: Help Adding up multiple calculated cells
- From: Pete_UK <pashurst@xxxxxxxxxxx>
- Date: Mon, 4 Feb 2008 17:33:41 -0800 (PST)
You will get #N/A errors if any of the VLOOKUPs are not able to find
an exact match. The way to avoid this in a single lookup case is:
=IF(ISNA(vlookup_formula),0,vlookup_formula)
so in your first case you could construct a formula along the lines
of:
= IF(Lic1<>"",IF(ISNA(vlookup_1),0,vlookup_1),0) +
IF(Lic2<>"",IF(ISNA(vlookup_2),0,vlookup_2),0) +
IF(Lic3<>"",IF(ISNA(vlookup_3),0,vlookup_3),0) +
IF(Lic4<>"",IF(ISNA(vlookup_4),0,vlookup_4),0) + ...
and so on. This arrangement will avoid the nested limit of 7 in XL2003
and earlier.
Not very pretty, I'm afraid, and you may run out of characters in the
cell, so you might have to arrange to have half of the formula in one
column and the other half in another column and add the two results
together.
Hope this helps.
Pete
On Feb 4, 7:22 pm, ssGuru <weca...@xxxxxxxxx> wrote:
I have 8 columns that MAY contain NOTHING or may contain text chosen
from a lookup name from a data validation pick list. Lic3 won't
contain a value UNLESS Lic1 AND Lic2 are populated.
I have 8 columns range named Lic1, Lic2, etc to hold a chosen license
name.
I have 8 other columns used for number counts relating to the lookups
I have range named Lic1Cnt, Lic2Cnt, etc which hold a number such as
12.
I need to add up the value calculated for each Lic based on looking up
the text in a table, getting the price and then multiplying by the Lic?
Cnt number. Some records may have only a single Lic1 entry while
others will have an entry in one or more of the Lic1-Lic8 fields.
This works just fine for a single entry such as :
=IF(Lic2 <>"",VLOOKUP(Lic2,LicTypeFeeTbl,2,FALSE)*Lic2Cnt,"")
I have successfully tested this type of calculation for each Lic1
through Lic8.
However I need help where multiple columns are concerned. I have tried
two different approaches where a IF THEN is used for each column and a
second approach where a single IF THEN is used. I want this cell to
return "" nothing IF there is no value in Lic1.
These SOMETIMES work but more often return #NA.
Here are both examples:
=IF(Lic1 <>"",VLOOKUP(Lic1,LicTypeFeeTbl,2,FALSE)*Lic1Cnt,"")+IF(Lic2
<>"",VLOOKUP(Lic2,LicTypeFeeTbl,2,FALSE)*Lic2Cnt,0)+IF(Lic3
<>"",VLOOKUP(Lic3,LicTypeFeeTbl,2,FALSE)*Lic3Cnt,0)+IF(Lic4
<>"",VLOOKUP(Lic4,LicTypeFeeTbl,2,FALSE)*Lic4Cnt,0)+IF(Lic5
<>"",VLOOKUP(Lic5,LicTypeFeeTbl,2,FALSE)*Lic5Cnt,0)+IF(Lic6
<>"",VLOOKUP(Lic6,LicTypeFeeTbl,2,FALSE)*Lic6Cnt,0)+IF(Lic7
<>"",VLOOKUP(Lic7,LicTypeFeeTbl,2,FALSE)*Lic7Cnt,0)+IF(Lic8
<>"",VLOOKUP(Lic8,LicTypeFeeTbl,2,FALSE)*Lic8Cnt,0)
=IF(Lic1 <>"",(VLOOKUP(Lic1,LicTypeFeeTbl,2,FALSE)*Lic1Cnt)+
(VLOOKUP(Lic2,LicTypeFeeTbl,2,FALSE)*Lic2Cnt)+
(VLOOKUP(Lic3,LicTypeFeeTbl,2,FALSE)*Lic3Cnt)+
(VLOOKUP(Lic4,LicTypeFeeTbl,2,FALSE)*Lic4Cnt)+
(VLOOKUP(Lic5,LicTypeFeeTbl,2,FALSE)*Lic5Cnt)+
(VLOOKUP(Lic6,LicTypeFeeTbl,2,FALSE)*Lic6Cnt)+
(VLOOKUP(Lic7,LicTypeFeeTbl,2,FALSE)*Lic7Cnt)+
(VLOOKUP(Lic8,LicTypeFeeTbl,2,FALSE)*Lic8Cnt),"")
Any ideas on why I get the #NA error and any ideas on a more efficient
formula?
Thanks, Dennis
.
- Follow-Ups:
- Re: Help Adding up multiple calculated cells
- From: ssGuru
- Re: Help Adding up multiple calculated cells
- References:
- Help Adding up multiple calculated cells
- From: ssGuru
- Help Adding up multiple calculated cells
- Prev by Date: Re: Pasting a Formula into a new column or worksheet, but so that it doesn't change
- Next by Date: Re: Pasting a Formula into a new column or worksheet, but so that it doesn't change
- Previous by thread: Help Adding up multiple calculated cells
- Next by thread: Re: Help Adding up multiple calculated cells
- Index(es):
Relevant Pages
|
