sum in a range of cells that contain letters and numbers

[anaop]

Hello everyone,

I need to sum all the numerical values (the numbers before the letter) in column A based on what letter they have. So, the result for "a" would be 3.5, "b" would be 3 etc. (as below in red)

Notes and restrictions: The only possible numerical values are :1 or 2 or 0.5. Letters can be : a, b, c, x, z. This column cannot be broken into 2 columns, one that stores letters and one that stores numbers, it all has to be in the same column.

The best I could come up with for now is something along the lines of:

=SUMPRODUCT((RIGHT(B2:B25,1)="a"), VALUE((LEFT(B2:B25,LEN(B2:B25)-1)))) - which isn't working, so this is why I need help.

What I am trying to do in the statement above is: for all the cells within Col B where the value ends with the letter "a", then sum up the (length -1) left corresponding characters (as 0.5 and 1 have varying lengths), while turning them into numbers, as they are perceived as text values.

I am not sure if I am even on the right track or my thinking might need to be revamped completely. Any help would be greatly appreciated.




[TABLE="width: 325"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]Col B[/TD]
[/TR]
[TR]
[TD]Client1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client2[/TD]
[TD]1a
[/TD]
[/TR]
[TR]
[TD]Client3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client4[/TD]
[TD]1b[/TD]
[/TR]
[TR]
[TD]Client5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client7[/TD]
[TD]1c[/TD]
[/TR]
[TR]
[TD]Client8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client11[/TD]
[TD]0.5a[/TD]
[/TR]
[TR]
[TD]Client12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client14[/TD]
[TD]2b[/TD]
[/TR]
[TR]
[TD]Client15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client19[/TD]
[TD]2a[/TD]
[/TR]
[TR]
[TD]Client20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client22[/TD]
[TD]0.5c[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a
[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

Hi,

Not sure I understand. Just taking a single cell, 0.5a say, what would be the result here? 0.5 * 3.5 = 1.75?

Regards

 

[Snakehips]

anaop,

Maybe....

Excel 2007
	A	B
1	Client1
2	Client2	1a
3	Client3
4	Client4	1b
5	Client5
6	Client6
7	Client7	1c
8	Client8
9	Client9
10	Client10
11	Client11	0.5a
12	Client12
13	Client13
14	Client14	2b
15	Client15
16	Client16
17	Client17
18	Client18
19	Client19	2a
20	Client20
21	Client21
22	Client22	0.5c
23
24	a	3.5
25	b	3
26	c	1.5
Sheet1

Cell Formulas
Range	Formula
B24	{=SUMPRODUCT((RIGHT($B$1:$B$22,1)=A24)*IFERROR(1*LEFT($B$1:$B$22,LEN($B$1:$B$22)-1),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.

 

[XOR LX]

Or, again, array formula**:

=SUM(IFERROR(0+SUBSTITUTE(B2:B25,{"a","b","c"},""),0)*{3.5,3,1.5})

Regards

 

[chrism216]

=+SUM(IFERROR(LEFT(B1:B4,LEN(B1:B4)-1),0)*1)*IFERROR((RIGHT(B1:B4,1)="a"),0))

Enter this formula with CTRL+SHIFT+ENTER.

Replace "B1:B4" to whatever array length you need.

Cheers

 

[anaop]

Snakehips - thank you so much for that - it works!!! Brilliant - thank you for nicely formatting the post (this has been my first ever post on a forum like this) and for the ctrl+shift+enter advice. I have no idea what this does as I've never heard of it before, but thank you so much. Greatly appreciated.

 

[Snakehips]

anaop,

You are welcome.

Here is a link to a You Tube video on array formulas videohttps://www.youtube.com/watch?v=vqfikSMx7mE

Here is a written explanation http://www.cpearson.com/excel/arrayformulas.aspx