Count the occurrence with the array formula

[mohsinbipu]

Hello Excel lovers,
For a better understanding, please find the picture. Here,

• my input Column is I, J, K
• My output column is the P column (I want to get the Xlookup result for nth number.)
• To count the number of occurrences, I add a helper column L

I don't want to add this helper column. I want to get the result directly from the input. So, as per my understanding, If I can get the number of occurrences with the array formula I don't need this helper column.

How I get this number of occurrences:
This is a bit tricky. To count the number of occurrences, I need to consider style ( I Column) and Qty ( K Column)

Example:-
Let's consider, Yellow highlighted Qty-209 (row-04). There are 04 times in this sheet. But 02 times for style 676841 02 and 02 times for 676841 04

So, for style 676841 02, the number of occurrences will be

• "1" in row 04 and "2" in row 11.

again for 676841 04, the number of occurrences will be

• "1" in row 12 and "2" in row 14.

Count the occurrence with the array formula.xlsx
	I	J	K	L	M	N	O	P
1
2	Input							Output column
3	Style	Date	Qty	Count Helper column		Style	Qty	Date
4	676841 02	16.01.2024	209	1		676841 02	279	12.01.2024
5	676841 02	15.01.2024	299	1		676841 02	299	15.01.2024
6	676841 02	12.01.2024	276	1		676841 02	298	18.01.2024
7	676841 02	12.01.2024	279	1		676841 02	276	12.01.2024
8	676841 02	16.01.2024	299	2		676841 02	209	16.01.2024
9	676841 02	18.01.2024	298	1		676841 02	299	16.01.2024
10	676841 02	13.01.2024	276	2		676841 02	276	13.01.2024
11	676841 02	13.01.2024	209	2		676841 02	209	13.01.2024
12	676841 04	16.01.2024	209	1
13	676841 04	15.01.2024	299	1
14	676841 04	12.01.2024	209	2
15	676841 04	12.01.2024	279	1
16	676841 05	16.01.2024	164	1
17	676841 05	18.01.2024	298	1
18	676841 05	13.01.2024	173	1
19	676841 05	13.01.2024	286	1
Sheet1

Cell Formulas
Range	Formula
P4:P11	P4	=XLOOKUP(N4 & O4 & COUNTIFS($O$4:O4, O4), I:I & K:K & L:L, J:J)
L4:L19	L4	=COUNTIFS($K$4:K4, K4, $I$4:I4, I4)

 

[mohsinbipu]

 

[Fluff]

How about

=CHOOSEROWS(FILTER($J$4:$J$1000,($I$4:$I$1000=N4)*($K$4:$K$1000=O4)),COUNTIFS(N$4:N4,N4,O$4:O4,O4))