Sum values held in a text string

**Jonsocks** · 01-13-2012, 07:32 AM

Hi all,

I've got quite a bit of data here that shows general information (held in a single text string) for someone over a calendar year, so each cell will represent the information for a particular person on one particular day. Each cell contains the following information: FTE (in 0.00 format), whether or not this 'person' is actually physically here 'R' or is a current vacancy 'V', their contract type (REG for regular, FIX for Fixed term or TEM, for an agency temp) and finally a three letter code for the team they currently sit on. A normal cell would look like this: 1.00RREGCHO.

What I'm trying (and currently failing) to do is to find a way to sum all of the data for a single day (there are currently 147 rows of data, but this will expand.) So I need to separate the first 4 characters of the reference, turn it into a value and then sum the resultant numbers. I also need to use SUMIF to break this down further, to get totals for a particular team or contract type etc.

I've had a look around but couldn't find anything related to this, apologies in advance if I've missed it.

Many thanks in advance as always

Jon

**martindwilson** · 01-13-2012, 07:49 AM

=SUM(IF(ISERROR(--LEFT(A1:A10,4)),0,--LEFT(A1:A10,4))) array entered should give sum

**adaws** · 01-13-2012, 07:50 AM

You can use left function and get the values for each cell, assuming 1.00RREGCHO is in cell A1 then formula will be...
=left(A1,4)*1 this will convert the text to a number and then u can use SUMIF and get the sum for a particular date
=sumif(range, "Criteria", Sum Range).. assuming the dates are in column B and the values are in Column C then function will be,

=sumif(B:B,"Enter Date",C:C)

**Marcol** · 01-13-2012, 07:51 AM

Unfortunately SUMIFS() is not as versatle as it could or should be.
Try this

Please Login or Register  to view this content.

where *1 is the numeric value of your first 4 characters. i.e. "1.00"

[EDIT]
There is a difference in interpretating your query
Martin is summing the whole column, I'm returning the sum for a specific value

**Fotis1991** · 01-13-2012, 07:52 AM

Hi Jon

For separate the values, of a referance in A1, use this formula(Not usefuull for excel<2007)

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;0;"");9;"");8;"");7;"");6;"");5;"");4;"");3;"");2;"");1;"");" ";" ")

I use Excel2003, so i can not test it now.

Hope to helps you.

**Jonsocks** · 01-13-2012, 08:49 AM

Thanks for such speedy responses! I'm currently working my way through them, thanks to martindwilson as that sum formula is working a treat, Marcol, if I wanted to sum the FTE in a range of cells as you've outlined above, but base it on slightly different criteria, (so to view the contract type I'd use "=RIGHT(LEFT(E2,8),3)" and then insert that into a formula that would return the sum of the FTE values) would that work?

Jon

**Pepe Le Mokko** · 01-13-2012, 08:53 AM

Originally Posted by Marcol

Please Login or Register  to view this content.

where *1 is the numeric value of your first 4 characters. i.e. "1.00"

I might be wrong but operating on "numeric" text automatically coerces the strings to numbers, so the multiplication by 1 is not necessary

**Marcol** · 01-13-2012, 09:02 AM

@ pepe
In this case you are correct, but consider the result if the first 4 characters are "2.00", or say "1.02". Then the multiplier is required.

**Haseeb Avarakkan** · 01-13-2012, 03:57 PM

Try this to get SUMIF for REG,

=SUMPRODUCT(--ISNUMBER(SEARCH("REG",A1:A148)),--LEFT(0&A1:A148,5))

For whole sum,

=SUMPRODUCT(--LEFT(0&A1:A148,5))

Note: There is 0& before the range.