Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 14th 05, 12:09 AM
Texas-DC_271
 
Posts: n/a
Default Help adding text values

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

  #2   Report Post  
Old January 14th 05, 01:18 AM
Max
 
Posts: n/a
Default

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



  #3   Report Post  
Old January 14th 05, 03:51 PM
DC
 
Posts: n/a
Default

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





  #4   Report Post  
Old January 15th 05, 01:26 AM
Max
 
Posts: n/a
Default

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



  #5   Report Post  
Old January 15th 05, 01:36 AM
Max
 
Posts: n/a
Default

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




  #6   Report Post  
Old January 15th 05, 08:29 AM
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

  #7   Report Post  
Old January 15th 05, 03:25 PM
DC
 
Posts: n/a
Default

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




  #8   Report Post  
Old January 15th 05, 11:14 PM
Max
 
Posts: n/a
Default

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding Leading Zeros to Text Jenn Excel Discussion (Misc queries) 4 January 12th 05 06:51 PM
Remove the apostrophe (') in Excel cell text values Connull Excel Discussion (Misc queries) 5 January 11th 05 05:06 PM
Stop Excel from converting text labels in CSV files to Values Just Want a Label! Excel Discussion (Misc queries) 1 January 11th 05 04:51 PM
Read Text File into Excel Using VBA Willie T Excel Discussion (Misc queries) 13 January 8th 05 12:37 AM
text and values combined in one cel Bart Excel Discussion (Misc queries) 1 December 14th 04 08:36 AM


All times are GMT +1. The time now is 06:59 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017