Count function

vmjan02

Well-known Member
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 Employee ID 1002792 1002734 Mon 1-Jun N1 N1 Tue 2-Jun N1 WO Wed 3-Jun WO N1 Thu 4-Jun WO N1 Fri 5-Jun N1 N1 Sat 6-Jun N1 N1 Sun 7-Jun N1 WO Mon 8-Jun N1 WO Tue 9-Jun N1 WO

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

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

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

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

Last edited:
CDE
12
2WO
3WO
4WO
5N1
6WO
7N1
8WO
9WO
10N1
11N1
12WO
13WO
14N1

<tbody>
</tbody>
CONTA CONSECUTIVI (2)

Array Formulas
CellFormula
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

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

Thanks a lot this is all fine

Hi,

thanks for providing feedback.

Just for sharing a (poor) approach:

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

It could work.

Regards

Replies
9
Views
278
Replies
4
Views
157
Replies
4
Views
161
Replies
10
Views
326
Replies
6
Views
329

1,207,170
Messages
6,076,914
Members
446,241
Latest member
Nhacai888b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back