View Single Post
  #6   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

Add an additional row to DATA, the one with 0's, sort the area in
ascending order on the first column as depicted...

DATA
0 0
A8 8
A9 9
S4 4
S8 8

Select the range of this 2-column table, excluding the row with the
label and name it DATA.

Let A2:F2 house the sample of entries you provided, H1 the symbol A, and
G the symbol S.

In H2 enter & copy to G2:

=SUMPRODUCT(LOOKUP($A$2:$F$2,DATA),--ISNUMBER(SEARCH(H1,$A$2:$F$2)))

Texas-DC_271 wrote:
I have a row of cells that I need to add. The row of cells do not
contain a number but rather a text value. I have created a table
linking my text values to my number values. The problem I have is
that each cell to be added may or may not have a value that is in the
table and I need the function to over look that problem.

Say I have 7 cells. Three of the cells are blank (or null), one of
the cells value is S4, another cells value is S8, and the other two
cells have a value of A8 and A9 respectively.
I need to add all of the cells but only want the values of the cells
that have a text value that starts with "A". The other cells should
be looked at as "0"

I have tried to use VLOOKUP but I would have to have over 38 different
VLOOKUP functions to add, and that is not working too well for me.

Is there an easer way that I just can't see?

DATA

S4 = 4
S8 = 8
A8 = 8
A9 = 9
"A" "S"
| A | B | C | D | E | F | G | H | I |
| | | | | | | | | |
| S4 | A8 | | S8 | | A9 | | 17 | 12 |

Thanks for any help possible,
David