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?