SUMPRODUCT and LEFT.

**klund** · 05-03-2009, 03:31 AM

Hello everyone.
Trying to get some statistics out of my golf!

My problem is i have in the cells 412,402,512 and so on, every number is a seperate stat.

I use this to count the first number.
=SUMPRODUCT(--(LEFT('Courses input'!B4:D4,1)="5"))/E4

It works fine. In the above range i would get 1 like i want.
But it is not very dynamic. I want the "5" to be a cell and i would also like to use a formula like D4+1 in that position.

I hope some of you excel masters can help me.

**zbor** · 05-03-2009, 04:34 AM

you can put: =SUMPRODUCT(--(1*LEFT(B4:D4,1)=A5))/E4

where a5 is dynamic cell

**windknife** · 05-03-2009, 04:35 AM

Try this

F4=5

=SUMPRODUCT(--(LEFT('Courses input'!B4:D4,1)=text(F4,"0")))/E4

**klund** · 05-03-2009, 06:07 AM

could not get windknife suggestion to work but zbor worked like a charm.
thank you very much

**klund** · 05-03-2009, 06:12 AM

got another one!
in the string you gave me zbor can i put another condition?

**zbor** · 05-03-2009, 06:19 AM

like what?

I would also put condition at begening IF(E5=0;"";Sumproduct....) so you avoid dividing by 0

Edit: off topic.. woo hoo 500 posts :D

**klund** · 05-03-2009, 06:50 AM

sorted out my last question and you answered my next question without me asking it. again thank you very much!

**klund** · 05-03-2009, 07:18 AM

could not get it to work.

My data columns look like this.
C D
4: 421
5: 312 365

This *** up the calculations in because D4 is empty
=SUMPRODUCT(--(1*LEFT('Courses input'!C4:D4,1)-C3<=0), --(1*MID('Courses input'!C4:D4,3,1)>=2))/E3

What can i do to exclude empty cells from calculation.

**Andy Pope** · 05-03-2009, 07:28 AM

Your post does not comply with Rule 6 of our Forum RULES. Common courtesy is the order of the day. Avoid coarse language, provide feedback to suggested solutions, and take the time to thank those who took their time to help you.