Help Adding up multiple calculated cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ssGuru - 04 Feb 2008 19:22 GMT

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

Reply to this Message

Pete_UK - 05 Feb 2008 01:33 GMT

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

> 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
[quoted text clipped - 45 lines]
>
> Thanks, Dennis

Reply to this Message

ssGuru - 06 Feb 2008 00:50 GMT

Thanks Pete,
I will give that structure a test try.

My formulas work, and as you say, IF there isn't a perfect match then
NA.

My formulas don't have a nested structure so that limit isn't a
problem.

I had hoped that someone would come up with a far more elegant and
much shorter formula for this process.

Dennis

Reply to this Message

Pete_UK - 06 Feb 2008 01:42 GMT

Well, it looks like you're stuck with my suggestions !! <bg>

At least they should get rid of the #N/A, even if they don't look very
elegant.

Pete

> Thanks Pete,
> I will give that structure a test try.
[quoted text clipped - 9 lines]
>
> Dennis

Reply to this Message