What would the formula be for...

  • Thread starter Thread starter derr04
  • Start date Start date
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
 
You need an *array* formula for this (commit with Shift+Ctrl+Enter)

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

HTH
Kostis Vezerides
 
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)
 
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
 
I should have read your post more carefully, to see that you have non-
contiguous cells. Ordinarily, Ron's suggestion should work.

Regards,
Kostis
 
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.
 
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)
 
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
 
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
 
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?
 
Back
Top