Max,
This helps me a little but, and I am very sorry, I am having a hard time
understanding your flow.
Let me see if I can explain this a little better.

I have a row of cells (D10:Q10) that I need to add. Those cells will
contain a text string that represents a number in a table on sheet2. Those
cells will either have an S value (S1, S1.25, S1.5, S1.75, S2, S2.25 and so
on to S10) or an A value (same as all the S values from A1 to A10) or the
cell will be blank.
I need to add all the cells (D10:Q10) in cell S10 but only want to add those
cells whose value starts with an A and all the other cells (cells that
contain an S value or blank) will be treated as 0.
In cell V10, I need to add all the cells (D10:Q10) but I only want add those
cells whose value starts with an S and again, all the other cells will be
treated as 0.
Sheet2 contains two, 2 column tables. Table 1 contains all the A values and
Table 2 contains all the S values. Each of these tables have 37 rows of
data. They are each identical except for the first column in each table.
They contain the text string that can be used in Sheet1!D10:Q10. Column 2
of each table contains the numeric value of the text string.

Now, I could be making this too hard on myself because the text string to be
added is the numeric value minus the first char. of the string, i.e... S4.75
would equal 4.75 and A8.25 would equal 8.25. Now if I could draw up a
function in cell (Sheet1!S10) that can be smart enough to look at each cell
to be added (Sheet1!D10:Q10) and only select those cells that contain a text
value that starts with an A, strip the A from the text string, and add those
cells while treating the other cells in the range as 0. In which case I
would need the same function in cell (Sheet1!V10) that would do the same as
the function in cell (Sheet1!S10) but only add those cells in the range that
contain a text value that starts with an S.

I know I could do all this in an Access database but for my application,
that's not an option. I hope what I am asking is possible.
Again, thanks for any help that you may be able to provide.

David
"Max" wrote in message
...
One way ..

Assume data below is in Sheet1,
cols A and B, in row2 down

S4 4
S8 8
A8 8
A9 9
etc

In Sheet2
-------------
Assuming B1:C1 contains: A, S

Put in B2:

=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$100,1)=B$1),Sheet1!$B$2:$B$10 0)

Copy across to C2

For the sample data in Sheet1:
B2 will return 17, C2 returns 12

Adapt the ranges to suit ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Texas-DC_271" wrote in message
...
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