Think we can try this ..
(we can dispense with the tables in Sheet2)

In Sheet1
------------
Put in V9: A
Put in W9: S

Put in the formula bar for V10:

=SUM(IF(--(LEFT($D10:$Q10,1)=V$9),--SUBSTITUTE($D10:$Q10,V$9,"")))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

V10 will return the desired result for "A", viz. it'll:

.... 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.

Copy V10 across to W10 to get the corresponding result for "S"

And if you have other rows below D10:Q10 to be similarly resolved,
just select V10:W10 and fill down

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