Left + Sum

[RF]

I have the following in A1

$2 Win
$2 Win
$2 Win

How do I total just the dollar amounts in cell B2? I thought it was
the left function, but I can't figure it out.

Thanks

 

[Ron Coderre]

If your source values are in A1:A10...
Try this:

=SUMPRODUCT(--(LEFT(A1:A10&" ",SEARCH(" ",A1:A10&"0 ")-1)))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

 

[Rick Rothstein \(MVP - VB\)]

I think you left out a "0" in the first concatenation....

=SUMPRODUCT(--(LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1)))

(I would have used FIND instead of SEARCH for this myself... it's shorter to
type.<g>)

Rick

 

[Sandy Mann]

I could be wrong - I was known to wrong once before<g> - but I needed a
SUBSTITUTE() function to get rid of the dollar sign as well:

=SUMPRODUCT(--(SUBSTITUTE(LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0
")-1),"$","")))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

The formula Ron posted (with the addition of the missing "0") worked fine
for me without having to handle the dollar sign separately (no matter what
formatting I gave to the cells in column A). Are you saying the formula
didn't work for you as written?

Rick

 

[Sandy Mann]

Hi Rick,

Yes that's right, when I copied the three values from the post into a
spreadsheet I ended up with three cells formatted as General but containing
text.

When I used your formula, hilighted just LEFT(A1:A10&"0 ",SEARCH("
",A1:A10&"0 ")-1) and pressed F9 I get
{"$2";"$2";"$2";"0";"0";"0";"0";"0";"0";"0"}returned. Including the next
set of parenthesis and the double unary I get
{#VALUE!;#VALUE!;#VALUE!;0;0;0;0;0;0;0}returned.

I assume that you get something different?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

When I examine the Left function results, I get 2 without the dollar sign. I
just tried several reformats and re-entries of the $2 Win text (including
with a leading apostrophe) and the dollar sign always gets removed and the
SUMPRODUCT always produces the correct summation.

Rick

 

[Sandy Mann]

Hi Rick,

Very curious. When I examine just: A1:A10&"0 " I get:
{"$2 Win0 ";"$2 Win0 ";"$2 Win0 ";"0 ";"0 ";"0 ";"0 ";"0 ";"0 ";"0 "}

If you are not getting the same then what is happening to the $?

If you are getting the same then the Search portion (with the -1) must
surely be returning:
{2;2;2;1;1;1;1;1;1;1}

So it seems to me that you are saying that if you enter: $2 Win
in cell A1 and then use the fomula:

=LEFT(A1&"0 ",SEARCH(" ",A1&"0 ")-1)
<the equivalent of =LEFT(A1,2) >

You are getting just 2 and I am getting $2

Is that right?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

I can swear something has changed, however....

Very curious. When I examine just: A1:A10&"0 " I get:
{"$2 Win0 ";"$2 Win0 ";"$2 Win0 ";"0 ";"0 ";"0 ";"0 ";"0 ";"0 ";"0 "}

If you are not getting the same then what is happening to the $?

Yes, I am now getting the dollar signs.

If you are getting the same then the Search portion (with the -1) must
surely be returning:
{2;2;2;1;1;1;1;1;1;1}

Yes, that is what I get also.

So it seems to me that you are saying that if you enter: $2 Win
in cell A1 and then use the fomula:

=LEFT(A1&"0 ",SEARCH(" ",A1&"0 ")-1)
<the equivalent of =LEFT(A1,2) >

You are getting just 2 and I am getting $2

Is that right?

Nope, I get the $2 for that formula also. However, if I put the double unary
in front of it...

=--LEFT(A1&"0 ",SEARCH(" ",A1&"0 ")-1)

it returns a 2 (which is why the SUMPRODUCT is producing the correct
summation using Ron's original formula modified to include the correction I
posted).

If you prefix the above LEFT function with the double unary, are you saying
you are not getting a 2 for a result?

Rick

 

[Sandy Mann]

Hi Rick,

Nope, I get the $2 for that formula also. However, if I put the double
unary in front of it...

=--LEFT(A1&"0 ",SEARCH(" ",A1&"0 ")-1)

it returns a 2

That's the difference! I get #VALUE!

I am using XL97 SR-1. I have never encountered any calculation difficulties
until no but I have heard that there were several. I will see if SR-2 is
still available on the MS site and see if that cures it.

Thank you.

--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Hi Again Rick,

Well I am all updated now, (if not up to date <g> ), but unfortunately it is
still the same so I asume that it is a version thing. Oh well, I will just
have to live with being the poor cousin.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

Yes, it probably is a version thing... I am using XL2003.

Rick

 

[Ron Coderre]

Hi, Rick

1) If the range may contain blank cells, yes....you need the zero you
inserted to accommodate them. Of course, everything still falls apart if
any cell only contains text or there's no space between the number and the
text. There's usually a trade-off between over-engineering a formula and
making it too limited to be used more broadly.

2) I purposely opted for the SEARCH function because it is not
case-sensitive. It doesn't matter when searching for a space, but the
formula is flexible enough to pull numbers prior to the first instance of
any character. (see comment 1)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)

(XL2003, Win XP)

 

[Ragdyer]

<<<"Yes, it probably is a version thing... ">>>

I really don't think so.

This morning I was in town and was following this thread on an XP - XL02
machine, and the returns were - accurate totals, without dollar signs.

This evening, I'm in the boonies, on a Win98 - XL97 SR-2 machine.

I get *exactly* the same returns that I got in town on the XL02 machine.

SO ... it has to be something else!

 

[Debra Dalgleish]

In Perth, the ancient capital of Scotland

and the crowning place of kings

And the land of the pound as default currency symbol?

 

[Rick Rothstein \(MVP - VB\)]

For point 1) I assumed you added the "0 " in the SEARCH function in order
to handle blank cells... if that were the case, then the missing zero I
pointed out for the other concatenation would be required.... otherwise, if
there were no blank cells, you wouldn't need to concatenate anything within
the formula at all... the following would work fine, assuming no blank cells
within the range...

=SUMPRODUCT(--(LEFT(A1:A10,SEARCH(" ",A1:A10)-1)))

Rick

 

[Rick Rothstein \(MVP - VB\)]

Well, that it is interesting, to say the least. I looked and didn't see any
(obvious) Option settings that would seem to control this, so I wonder what
the problem with Sandy's Excel could be then?

Rick

 

[Ragdyer]

Don't you see Debra's post?

Refers to Sandy's currency setting.

 

[Rick Rothstein \(MVP - VB\)]

Yes, I saw it... but misunderstood her comment. I skimmed it and thought she
was making a joke in response to his tag line.

So, his (and many others) currency is not the dollar sign... this raises the
"international audience" question then... theoretically, Sandy's inclusion
of the SUBSTITUTE function would make the formula universally usable. Of
course, the odds are that someone typing in "$2 Win" in his/her own
spreadsheet would not be too concerned with Regional Setting's issues
though.<g>

Rick

 

[Wild Bill]

With 97 SR-1,
Each of [General format] A1:A3 contain the 6 bytes $2 Win
=SUMPRODUCT(--(LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1)))
produces 6. So, all fine, Rick-nee-Ron.

When I examine the Left function results, I get 2 without the dollar sign.
I just tried several reformats and re-entries of the $2 Win text (including
with a leading apostrophe) and the dollar sign always gets removed and the
SUMPRODUCT always produces the correct summation.

B1=LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1)
gives two bytes here
$2

I assume that's what you meant by "examine the Left" but do correct me
if I misunderstood. Note that the LEFT result as I showed above (in
isolation from sumproduct) gives a relative result (relative to current
row), in case that tripped anyone up. Thus the same formula in B4 gives
0.

So Rick, there's a $ on this version.
Sandy, I don't duplicate, but perhaps I have different assumptions
above. Or...
Anybody, feel free to note new thread "Using multicell range when single
cell prescribed" (featuring "VALUE" perhaps significant to earlier posts
in this thread.)