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

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

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

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

Put in the formula bar for V10:

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

Clarification:

Think we could drop the "--(...)" for the
... LEFT($D10:$Q10,1)=V$9) .. part

So, maybe revise the formula in V10 to:

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

Array-enter with CTRL+SHIFT+ENTER, as before

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

Max,
Thank you very much!!!
This did the trick...

One last thing, I tried to use an =IF statement to make the formula cell
blank if the value is 0, but for some reason it did not work. It gave me
the #Name error.
Here is what I tried, please let me know if I did something wrong.
=IF(S10=0,"",{=SUM(IF(--(LEFT($D10:$Q10,1)=S$9),--SUBSTITUTE($D10:$Q10,S$9,"
")))})

Thanks again,
David

"Max" wrote in message
...
Put in the formula bar for V10:

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

Clarification:

Think we could drop the "--(...)" for the
.. LEFT($D10:$Q10,1)=V$9) .. part

So, maybe revise the formula in V10 to:

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

Array-enter with CTRL+SHIFT+ENTER, as before

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

One way to quickly / easily suppress extraneous zeros from showing:
Click Tools Options View tab Uncheck Zero values OK
(I'd go for this option)

If we want the formula in V10 to return blank when the result is zero,
think we'd need to put instead in V10:

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

with the formula array-entered, as before

Note that the curly braces { } around the formula have to be inserted by
Excel upon array-entering. We *don't* type these curly braces into the
formula <g

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"DC" wrote in message
...
Max,
Thank you very much!!!
This did the trick...

One last thing, I tried to use an =IF statement to make the formula cell
blank if the value is 0, but for some reason it did not work. It gave me
the #Name error.
Here is what I tried, please let me know if I did something wrong.

=IF(S10=0,"",{=SUM(IF(--(LEFT($D10:$Q10,1)=S$9),--SUBSTITUTE($D10:$Q10,S$9,"
")))})

Thanks again,
David