If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Summing Cells with Text and Numbers
Send me a sample file - get my email from my website
best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Trevor Little" wrote in message ... Good morning Bernard, I triedusing the MID formula and it is still turning up a result of "#VALUE!" when a cell is blank. The formula I am using is: =SUMPRODUCT((LEFT(C29:AG29,1)="S")*VALUE((MID(C29: AG29,2,20)))) The data that is in those cells are the same as below: (EG. V8, S10, T6, B4, S4... etc and blanks) Thanks again for your patience. Trevor "Bernard Liengme" wrote: =SUMPRODUCT((LEFT(A11)="S")*VALUE((MID(A11,2,2 0)))) VALUE is needed if there are any empty cells Bernard "Trevor Little" wrote in message ... Hi Bernard, I just came across one problem with the formula that I haven't been able to figure out. It doesn't seem to work, if 1 of the cells in the array is blank... is there a way of putting in a condition where it ignores the blank cells? Thanks again. "Bernard Liengme" wrote: =SUMPRODUCT((LEFT(A11)="S")*(MID(A11,2,20))) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Trevor Little" Trevor wrote in message ... Is it possible to use the Sumif function to add numbers with a specific designator in front of it? Eg. A row contains strings like: S8, S10, V5, S5 Is there a formula that can total the S's? (Total would be 23) . . |
#12
|
|||
|
|||
Summing Cells with Text and Numbers
... It doesn't seem to work, if 1 of the cells in the array is
blank... is there a way of putting in a condition where it ignores the blank cells? Try this variant, array-enter (press CTRL+SHIFT+ENTER): =SUM(IF(NOT(ISBLANK(A11)),IF(LEFT(A11,1)="S",R IGHT(A11,LEN(A11)-1)+0))) ---- Max Singapore |
|
Thread Tools | |
Display Modes | |
|
|