Count function

[vmjan02]

Hi,

My concern in a column I want to count as how many times an consequitive WO is been given or one by one.

eg:- In column C the WO is on 3 and 4 june so the formula or macro shound count it as 1.

Day</SPAN>	Employee ID</SPAN>	1002792</SPAN>	1002734</SPAN>
Mon</SPAN>	1-Jun</SPAN>	N1	N1
Tue</SPAN>	2-Jun</SPAN>	N1	WO
Wed</SPAN>	3-Jun</SPAN>	WO	N1
Thu</SPAN>	4-Jun</SPAN>	WO	N1
Fri</SPAN>	5-Jun</SPAN>	N1	N1
Sat</SPAN>	6-Jun</SPAN>	N1	N1
Sun</SPAN>	7-Jun</SPAN>	N1	WO
Mon</SPAN>	8-Jun</SPAN>	N1	WO
Tue</SPAN>	9-Jun</SPAN>	N1	WO

<TBODY>
</TBODY><COLGROUP><COL span=4></COLGROUP>

 

[Canapone]

Hi,

a first attempt

=SUMPRODUCT((C$2:C$100="WO")*(C$2:C$100=C$3:C$101))

In column D formula would count 2 (7-8 Jun + 8-9 Jun)

Hope it helps

 

[vmjan02]

Hi,

a first attempt

=SUMPRODUCT((C$2:C$100="WO")*(C$2:C$100=C$3:C$101))

In column D formula would count 2 (7-8 Jun + 8-9 Jun)

Hope it helps

Cool this is working fine but in some case if the WO is 3 times in a column then it should not count. Any idea for the same?

eg:- In column D the WO one after the other is on 15 and 16 June then it should count it as 1.

Day</SPAN>	Employee ID</SPAN>	1002792</SPAN>	1002734</SPAN>
Mon</SPAN>	1-Jun</SPAN>	N1	N1
Tue</SPAN>	2-Jun</SPAN>	N1	WO
Wed</SPAN>	3-Jun</SPAN>	WO	N1
Thu</SPAN>	4-Jun</SPAN>	WO	N1
Fri</SPAN>	5-Jun</SPAN>	N1	N1
Sat</SPAN>	6-Jun</SPAN>	N1	N1
Sun</SPAN>	7-Jun</SPAN>	N1	WO
Mon</SPAN>	8-Jun</SPAN>	N1	WO
Tue</SPAN>	9-Jun</SPAN>	N1	WO
Wed</SPAN>	10-Jun</SPAN>	N1	N1
Thu</SPAN>	11-Jun</SPAN>	WO	N1
Fri</SPAN>	12-Jun</SPAN>	WO	N1
Sat</SPAN>	13-Jun</SPAN>	N1	N1
Sun</SPAN>	14-Jun</SPAN>	N1	N1
Mon</SPAN>	15-Jun</SPAN>	N1	WO
Tue</SPAN>	16-Jun</SPAN>	N1	WO

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=2></COLGROUP>

 

[Canapone]

	C	D	E
1			2
2	WO
3	WO
4	WO
5	N1
6	WO
7	N1
8	WO
9	WO
10	N1
11	N1
12	WO
13	WO
14	N1

<tbody>
</tbody>

CONTA CONSECUTIVI (2)​

Array Formulas Cell Formula E1 {=SUM(IF(FREQUENCY(IF(C2:C100="WO",ROW(A2:A100)-1),IF(C2:C100<>"WO",ROW(A1:A100)-1))=2,1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope it helps

 

[Aladin Akyurek]

Care to re-word what you are after? What are you counting for each id?

 

[vmjan02]

Thanks a lot this is all fine

 

[Canapone]

Hi,

thanks for providing feedback.

Just for sharing a (poor) approach:

=SUMPRODUCT((C2:C100="WO")*(C3:C101="WO")*(C1:C99<>"WO")*(C4:C102<>"WO"))

It could work.

Regards