S
ssGuru
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
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