What would the formula be for...

D

derr04

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
 
V

vezerid

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

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

HTH
Kostis Vezerides
 
R

Ron Coderre

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)
 
D

derr04

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

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

HTH
Kostis Vezerides




- Show quoted text -
Click to expand...

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
 
V

vezerid

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

Regards,
Kostis
 
D

derr04

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)







- Show quoted text -
Click to expand...

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.
 
R

Ron Coderre

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)
 
R

Rick Rothstein \(MVP - VB\)

Try this ARRAY FORMULA(committed with Ctrl+Shift+Enter, instead of just
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.
Click to expand...

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
 
D

derr04

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

Regards,
Kostis




- Show quoted text -
Click to expand...

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

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

derr04

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 -
Click to expand...

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?
 
Top