Counting with 2 criteria up and down

[FSalgueiro]

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Site[/TD]
[TD]Product[/TD]
[TD]UOM[/TD]
[TD]Product ID[/TD]
[TD]ID[/TD]
[TD]Value[/TD]
[TD]Sale[/TD]
[TD][/TD]
[TD]Counter Results[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Pen[/TD]
[TD]9[/TD]
[TD]26525802[/TD]
[TD]77587241[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Pencil[/TD]
[TD]9[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]32266576[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]77586563[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Pen[/TD]
[TD]9[/TD]
[TD]47163111[/TD]
[TD]77595902[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Sharpie[/TD]
[TD]9[/TD]
[TD]49726635[/TD]
[TD]77592873[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Pen[/TD]
[TD]9[/TD]
[TD]26525802[/TD]
[TD]77583416[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Rubber[/TD]
[TD]9[/TD]
[TD]60718288[/TD]
[TD]60718288[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Pen[/TD]
[TD]9[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]62748567[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]60718288[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Light[/TD]
[TD]9[/TD]
[TD]12354698[/TD]
[TD]82045666[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I am trying to run counting the numbers of the same site with the same product looking for up and down
records.

 

[etaf]

dont understand the results and question

same site with the same product
A Pen = 2 you have 1
A Sharpie = 1 you have 3

can you expand

 

[Aladin Akyurek]

In I2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:A2=A2,IF($B$2:B2<>"",IF($C$2:C2=C2,MATCH($B$2:B2,$B$2:B2,0)))),ROW($A$2:A2)-ROW($A$2)+1),1))

For efficiency reasons, blank cells are not created.

 

[shaowu459]

I2：

=SUM((A$2:A2=A2)/COUNTIFS(A$2:A2,A$2:A2,B$2:B2,B$2:B2))

Ctrl shift enter, copy down.

 

[Aladin Akyurek]

I2：

=SUM((A$2:A2=A2)/COUNTIFS(A$2:A2,A$2:A2,B$2:B2,B$2:B2))

Ctrl shift enter, copy down.

No need for re-write as the frequency version is faster: https://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

 

[FSalgueiro]

just got 1 - 0.5 - 1 - 0.5 - 1 - 0.50 - 0.33 is not counting

 

[Aladin Akyurek]

just got 1 - 0.5 - 1 - 0.5 - 1 - 0.50 - 0.33 is not counting

See post #3 ...

Control+shift+enter means: Press down the shift and the control keys while you hit the neter key. When done successfully, Excel itself puts a pair of { and } around the formula in recognition.