Home 
Search 
Today's Posts 
#1




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




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




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




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,""))) Arrayenter 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




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,""))) Arrayenter with CTRL+SHIFT+ENTER, as before  Rgds Max xl 97  GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom  
#6




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 2column 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))) TexasDC_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




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,""))) Arrayenter with CTRL+SHIFT+ENTER, as before  Rgds Max xl 97  GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom  
#8




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 arrayentered, as before Note that the curly braces { } around the formula have to be inserted by Excel upon arrayentering. 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 
Display Modes  


Similar Threads  
Thread  Forum  
Adding Leading Zeros to Text  Excel Discussion (Misc queries)  
Remove the apostrophe (') in Excel cell text values  Excel Discussion (Misc queries)  
Stop Excel from converting text labels in CSV files to Values  Excel Discussion (Misc queries)  
Read Text File into Excel Using VBA  Excel Discussion (Misc queries)  
text and values combined in one cel  Excel Discussion (Misc queries) 