Countifs with repeated values

[olukay]

Hi there ,

Please help

[TABLE="width: 500"]
<tbody>[TR]
[TD]month[/TD]
[TD]worker[/TD]
[TD]month[/TD]
[TD]no of workers[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]john[/TD]
[TD]jan[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]james[/TD]
[TD]feb[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]james[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]james[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]james[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

i need a formular in the cells with question marks. The formular should return the number of workers in each month. Eg January should be 2 while february should be 1.

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]month[/TD]
[TD]worker[/TD]
[TD]month[/TD]
[TD]no of workers[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Jan[/TD]
[TD]john[/TD]
[TD]jan[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Jan[/TD]
[TD]james[/TD]
[TD]feb[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Jan[/TD]
[TD]james[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Feb[/TD]
[TD]james[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Feb[/TD]
[TD]james[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


D2=IFERROR(SUM(IF(FREQUENCY(IF($A$2:$A$100=$C2,MATCH($B$2:$B$100,$B$2:$B$100,0)),ROW($B$2:$B$100)-ROW($B$2)+1),1)),"")


control +shift +enter copy down

 

[olukay]

This works perfectly. MANY THANKS!

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option with a few less function calls, also c+s+e confirmation

Excel Workbook
	A	B	C	D
1	month	worker	month	no of workers
2	Jan	john	jan	2
3	Jan	james	feb	1
4	Jan	james
5	Feb	james
6	Feb	james
Counts