Count a number just the first time it appears in each string.

[Marvo]

In the image B2 copied down is =IF(($A2="League")*($B2="H"),IF($C2="W",SUM(1,D1),0),N(D1))

It's counting consecutive winning home league games. The result is 12.

However if you put a formula at the bottom of Column D, =COUNTIF(D2:D34,E14), it would give you a result of 4. But of course you've only reached 12 once.

Is there another way of asking the question to get a result of 1, or if further down the column there was another run of 13, a result of 2?

I can't think of a way.

Any help appreciated.

 

[StephenCrump]

What about: =COUNTIFS(A2:A34,"League",B2:B34,"H",D2:D34,E14)

 

[Marvo]

That's fabulous Stephen, certainly works in the example given, now to try it in the big workbook. Thank you very much.

 

[Marvo]

Hi again Stephen, couldn't get it to work in the larger workbook.

I've entered =IF(C25="","",COUNTIFS(ALL!$J2:$J5558,A25,ALL!$B2:$B5558,B25,ALL!$BB$2:$BB$5558,C25))

C25 = 12
A25 = League
B25 = H

There are two instances of 12 in the column and they should only count as 1 as they are in the same string, however the result I'm getting is Zero. Can you see where I've gone wrong please?

 

[offthelip]

what about:

=COUNTIFS(D2:D34,E14,D1:D33,E14-1)

 

[Marvo]

I'd made a mistake in the formula Stephen gave me. Having rectified it, the result I'm getting is 2. There are indeed 2 instances of 12 in the column bitbthey are part of the same string. The result I'm after is 1.

 

[StephenCrump]

There are two instances of 12 in the column and they should only count as 1 as they are in the same string, however the result I'm getting is Zero.

I'd made a mistake in the formula Stephen gave me. Having rectified it, the result I'm getting is 2.

It's a little hard to know what's going on without seeing your layout. Can you post a sample using XL2BB - Excel Range to BBCode?

 

[Marvo]

Okay, its sorted, schoolboy error. Thanks very much, your formula does indeed work Stephen. Much obliged and sorry for the misunderstanding.

 

[StephenCrump]

Okay, its sorted, schoolboy error ...

Ha ha, no problem. I'm glad you got it working