CSE Formula Required

[jim may]

I have below range of Text Values - I wish to have a formula in Cell B85 as follows emtered with CSE to produce({}):
to get result of: 190.51

={SUM(LEFT(B72:B84,FIND(" ",B72:B84,1)-1)+0)} But I'm Getting #VALUE ! How do I need to Adjust above existing Formula?

<tbody>
</tbody>

Excel 2010

	B
72	30.51 for Wk
73	40 for Wk
74	40 for Wk
75	40 for Wk
76	40 for Wk
77
78
79
80
81
82
83
84

<tbody>
</tbody>

Quick-Cash_Proj​

 

[Fluff]

Remove the {} from your formula, they should not be entered manually.
The whilst in the formula bar press Ctrl Shift Enter & the {} should then appear

 

[Logit]

When I entered the following into the empty cells, your formula using CSE delivers the value you were looking for :

	A​	B​
70​
71​
72​		30.51 for Wk
73​		40 for Wk
74​		40 for Wk
75​		40 for Wk
76​		40 for Wk
77​		0 for Wk
78​		0 for Wk
79​		0 for Wk
80​		0 for Wk
81​		0 for Wk
82​		0 for Wk
83​		0 for Wk
84​		0 for Wk
85​		190.51​

 

[jim may]

I understand what you are saying -- in my formula the {}'s were NOT entered manually.

It seems the problem with my formula is the Blank Cells in the Range B77:B84.
When I alter the Range to ONLY B72:B76 it Works. Still prefer to use full range B72:B84.

What must I do?

Thanks,
Jim

 

[Fluff]

I assumed that you had entered the braces manually as the first one is in the wrong place.
Try

Excel 2013/2016
	B
1	30.51 for Wk
2	40 for Wk
3	40 for Wk
4	40 for Wk
5	40 for Wk
6
7
8
9
10
11
12
13
14
15
16
17
18
19	190.51
Master

Cell Formulas
Range	Formula
B19	{=SUM(IF(B1:B18<>"",LEFT(B1:B18,FIND(" ",B1:B18,1)-1)+0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tetra201]

@ jim may:

Here is how to tweak your formula:

=SUM(LEFT(0&B72:B84,FIND(" ",0&B72:B84&" ",1)-1)+0)

Or even shorter:

=SUM(LEFT(0&B72:B84,FIND(" ",B72:B84&" ",1))+0)

 

[jim may]

Fluff - thanks for the additionsl IF(B1:B18<>"",
Works a treat, as they say...

Jim

 

[jim may]

Nice Alternative... Tetra201

Tks,

Jim

 

[Fluff]

You're welcome & thanks for the feedback.