Sum cells containing characters

**Scrap74** · 06-20-2009, 02:22 AM

Hi All,

Had a bit of a look around for a solution to my problem, but cant seem to get anything to work...

I have a worksheet containing values for staff that are either on-site at work or on R&R, see below:

RR1
1
1
1
1
-
RR1
RR1
RR2
RR4

The values without a prefix of "RR" are easily summed up. I need to sum the values that are prefixed with "RR" separately.

In the example above I need 2 rows, one that sums the people on-site (=4) and the people on RR (=9).

If I get it sorted I will extend the codes to include AL (Annual Leave), etc...

Any ideas?

**DonkeyOte** · 06-20-2009, 02:27 AM

If we assumed your data was in range A1:A10, in B1 you enter RR then to return days:

C1: =SUM(IF(LEFT($A$1:$A$10,LEN($B1))=$B1,--REPLACE($A$1:$A$10,1,LEN($B1),"")))
committed with CTRL + SHIFT + ENTER

same logic can be applied elsewhere, ie if B2 holds next string of interest you can simply copy the formula down

Note: in Arrays pre-XL07 you can't use entire column references (A:A) nor should you, keep ranges to a min so as to minimise impact on performance.