Counting with 2 criteria up and down

[FSalgueiro]

Site	Product	UOM	Product ID	ID	Value	Sale		Counter Results
A	Pen	9	26525802	77587241	1	2		1
A	Pencil	9	32266576 <tbody> </tbody>	77586563 <tbody> </tbody>	2	3		2
A	Pen	9	47163111	77595902	3	4
A	Sharpie	9	49726635	77592873	1	8		3
B	Pen	9	26525802	77583416	8	9		1
B	Rubber	9	60718288	60718288	2	4		2
B	Pen	9	62748567 <tbody> </tbody>	60718288 <tbody> </tbody>	2	3
B	Light	9	12354698	82045666	1	2		3

<tbody>
</tbody>

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.