If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help

You need an *array* formula for this (commit with Shift+Ctrl+Enter)

=SUM(--LEFT(A1:A3,FIND(" ",A1:A3)-1))

HTH
Kostis Vezerides

On Oct 19, 7:59 pm, wrote:
If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help

On Oct 19, 1:08 pm, vezerid wrote:
You need an *array* formula for this (commit with Shift+Ctrl+Enter)

=SUM(--LEFT(A1:A3,FIND(" ",A1:A3)-1))

HTH
Kostis Vezerides

On Oct 19, 7:59 pm, wrote:



If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help- Hide quoted text -

- Show quoted text -

I thought the array was my only way to go, but when i try to subout
where you have A1:A3 with the cells i am trying to use I get #Value
everytime and cant seem to figure out why

I should have read your post more carefully, to see that you have non-
contiguous cells. Ordinarily, Ron's suggestion should work.

Regards,
Kostis

On Oct 19, 8:15 pm, wrote:
On Oct 19, 1:08 pm, vezerid wrote:



You need an *array* formula for this (commit with Shift+Ctrl+Enter)

=SUM(--LEFT(A1:A3,FIND(" ",A1:A3)-1))

HTH
Kostis Vezerides

On Oct 19, 7:59 pm, wrote:

If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help- Hide quoted text -

- Show quoted text -

I thought the array was my only way to go, but when i try to subout
where you have A1:A3 with the cells i am trying to use I get #Value
everytime and cant seem to figure out why

Try this ARRAY FORMULA(committed with Ctrl+Shift+Enter, instead of just
Enter):

=SUMPRODUCT(IF(ROW(B1:B32)={1,16,32},--LEFT(B1:B32,FIND(" ",B1:B32)-1)))

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

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

wrote in message
ups.com...
If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help

On Oct 19, 1:10 pm, "Ron Coderre"
wrote:
Try this ARRAY FORMULA(committed with Ctrl+Shift+Enter, instead of just
Enter):

=SUMPRODUCT(IF(ROW(B1:B32)={1,16,32},--LEFT(B1:B32,FIND(" ",B1:B32)-1)))

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

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

wrote in message

ups.com...



If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help- Hide quoted text -

- Show quoted text -

Well the formula you supplied did give me an answer it gave me the
result of 6......clearly 16 is what we are looking for it appears with
your formula whatever i put in B16 is what it gives me....if i put 8 -
4 wins it gives me a result of 8....no matter what is in the other
cells.

Hmmmm....
To commit the formula, did you hold down Ctrl+Shift and press Enter, instead
of just pressing Enter?

Also...the formula I posted had an incorrect row ref (1, instead of 3).
Try this:
=SUMPRODUCT(IF(ROW(B1:B32)={3,16,32},--LEFT(B1:B32,FIND(" ",B1:B32)-1)))

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

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)



wrote in message
oups.com...
On Oct 19, 1:10 pm, "Ron Coderre"
wrote:
Try this ARRAY FORMULA(committed with Ctrl+Shift+Enter, instead of just
Enter):

=SUMPRODUCT(IF(ROW(B1:B32)={1,16,32},--LEFT(B1:B32,FIND(" ",B1:B32)-1)))

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

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

wrote in message

ups.com...



If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help- Hide quoted text -

- Show quoted text -

Well the formula you supplied did give me an answer it gave me the
result of 6......clearly 16 is what we are looking for it appears with
your formula whatever i put in B16 is what it gives me....if i put 8 -
4 wins it gives me a result of 8....no matter what is in the other
cells.

Try this ARRAY FORMULA(committed with Ctrl+Shift+Enter, instead of just
Enter):

=SUMPRODUCT(IF(ROW(B1:B32)={1,16,32},--LEFT(B1:B32,FIND(" ",B1:B32)-1)))

If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help- Hide quoted text -

- Show quoted text -

Well the formula you supplied did give me an answer it gave me the
result of 6......clearly 16 is what we are looking for it appears with
your formula whatever i put in B16 is what it gives me....if i put 8 -
4 wins it gives me a result of 8....no matter what is in the other
cells.

Ron had a typo in his formula (the 1 in the braces should have been a 3)...

=SUMPRODUCT(IF(ROW(B1:B32)={3,16,32},--LEFT(B1:B32,FIND(" ",B1:B32)-1)))

And remember, you must commit this formula using Ctrl+Shift+Enter, **not**
just using Enter by itself.

Rick

On Oct 19, 1:45 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Try this ARRAY FORMULA(committed with Ctrl+Shift+Enter, instead of just
Enter):

=SUMPRODUCT(IF(ROW(B1:B32)={1,16,32},--LEFT(B1:B32,FIND(" ",B1:B32)-1)))

If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help- Hide quoted text -

- Show quoted text -

Well the formula you supplied did give me an answer it gave me the
result of 6......clearly 16 is what we are looking for it appears with
your formula whatever i put in B16 is what it gives me....if i put 8 -
4 wins it gives me a result of 8....no matter what is in the other
cells.

Ron had a typo in his formula (the 1 in the braces should have been a 3)...

=SUMPRODUCT(IF(ROW(B1:B32)={3,16,32},--LEFT(B1:B32,FIND(" ",B1:B32)-1)))

And remember, you must commit this formula using Ctrl+Shift+Enter, **not**
just using Enter by itself.

Rick- Hide quoted text -

- Show quoted text -

yea now that you guys pointed out the 3 instead of 1 error it works
great....exactly what i was looking for and since it seems I have your
attention at the moment, I would like to take advantage of your vast
knowledge

in another situation I am using the formula of =SUMPRODUCT(--
LEFT(SUBSTITUTE(B3:B14,"(",""),FIND(" ",B3:B14)-1))

to do basically the same thing of counting the row totals of various
things that also have text at the end right now for all the blank
cells I have plugged in 0 - Empty because thats the only way it seems
to work but I was wondering if there is anyway that I can get that to
work with just leaving the cells blank?

It is an *array* formula. Commit with Shift+Ctrl+Enter.

Regards,
Kostis

On Oct 19, 8:24 pm, wrote:
On Oct 19, 1:10 pm, "Ron Coderre"
wrote:



Try this ARRAY FORMULA(committed with Ctrl+Shift+Enter, instead of just
Enter):

=SUMPRODUCT(IF(ROW(B1:B32)={1,16,32},--LEFT(B1:B32,FIND(" ",B1:B32)-1)))

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

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

wrote in message

oups.com...

If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help- Hide quoted text -

- Show quoted text -

Well the formula you supplied did give me an answer it gave me the
result of 6......clearly 16 is what we are looking for it appears with
your formula whatever i put in B16 is what it gives me....if i put 8 -
4 wins it gives me a result of 8....no matter what is in the other
cells.

On Oct 19, 1:46 pm, vezerid wrote:
It is an *array* formula. Commit with Shift+Ctrl+Enter.

Regards,
Kostis

On Oct 19, 8:24 pm, wrote:



On Oct 19, 1:10 pm, "Ron Coderre"
wrote:

Try this ARRAY FORMULA(committed with Ctrl+Shift+Enter, instead of just
Enter):

=SUMPRODUCT(IF(ROW(B1:B32)={1,16,32},--LEFT(B1:B32,FIND(" ",B1:B32)-1)))

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

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

wrote in message

oups.com...

If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help- Hide quoted text -

- Show quoted text -

Well the formula you supplied did give me an answer it gave me the
result of 6......clearly 16 is what we are looking for it appears with
your formula whatever i put in B16 is what it gives me....if i put 8 -
4 wins it gives me a result of 8....no matter what is in the other
cells.- Hide quoted text -

- Show quoted text -

=LEFT(B3,1)+LEFT(B16,1)+LEFT(B32,1)

this worked perfectly....thanks for your help everyone

=LEFT(B3,1)+LEFT(B16,1)+LEFT(B32,1)
--
Gary''s Student - gsnu200750


" wrote:

If i have in cell B3 4 - 2 Wins, cell B16 6 - 3 Wins, and cell B32 6 -
3 Wins.....i want to add those three cells and come out with an answer
of 16...i want it to ignore the text of how many wins and just add the
first number

thanks ahead of time for your help