Excel Formula Help

[Anonnymouse]

Hello Excel Users,

I am currently using formula =FILTER(CHOOSECOLS(B3:H100,1,2,5,6,7),(E3:E100=A3)*(H3:H100<>I3)), I am wondering if I could add a expression that will count the number of times the Order# shows up and paste in cell O.

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
	Sample Data								Formula Data
	Order#	Date	No	Error Code	Old Price	New Price	User		Order#	Date	Old Price	New Price	User	Number of Times
	512878	1/1/2023	1	Err_1944	200	300	Atlanta	Atlanta	971110	1/8/2023	900	10	Frank	2
	512878	1/4/2023	2	Err_1832	100	105	Boston		428753	1/10/2023	899	399	Henry	1
Err_1944	654888	1/5/2023	3	Err_0782	400	200	Colton		971110	1/9/2023	1000	500	Frank	2
	654888	1/6/2023	4	Err_1480	600	800	Delta
	971110	1/7/2023	5	Err_1832	50	100	Echo
	971110	1/8/2023	6	Err_1944	900	10	Frank
	971110	1/9/2023	7	Err_1480	1090	350	George
	428753	1/10/2023	8	Err_1944	899	399	Henry
	971110	1/9/2023	6	Err_1944	1000	500	Frank

 

[JamesCanale]

In O3

=COUNTIFS(B3:B100,TAKE(J3#,,1),E3:E100,A3,H3:H100,"<>"&I3)

This applies the same filters as used to create the array in J3#.

 

[Anonnymouse]

In O3

=COUNTIFS(B3:B100,TAKE(J3#,,1),E3:E100,A3,H3:H100,"<>"&I3)

This applies the same filters as used to create the array in J3#.

When i put this formula in O3 it creates a spill error in cell J3

 

[JamesCanale]

J3# spills bigger than shown in the example. Put the new formula further to the right - wherever J3# isn't spilling.

 

[Anonnymouse]

The formula is working but unfortunately now it shows zero instead of showing the answer as 2 and 1

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Sample Data								Formula Data
2		Order#	Date	No	Error Code	Old Price	New Price	User		Order#	Date	Old Price	New Price	User	Number of Times
3		512878	1/1/2023	1	Err_1944	200	300	Atlanta	Atlanta	971110	1/8/2023	900	10	Frank	0	0
4		512878	1/4/2023	2	Err_1832	100	105	Boston		428753	########	899	399	Henry	1	0
5		654888	1/5/2023	3	Err_0782	400	200	Colton		971110	1/9/2023	1000	500	Frank	2	0
6	Err_1944	654888	1/6/2023	4	Err_1480	600	800	Delta
7		971110	1/7/2023	5	Err_1832	50	100	Echo
8		971110	1/8/2023	6	Err_1944	900	10	Frank
9		971110	1/9/2023	7	Err_1480	1090	350	George
10		428753	########	8	Err_1944	899	399	Henry
11		971110	1/9/2023	6	Err_1944	1000	500	Frank

Do you know what I am doing wrong?

 

[JamesCanale]

Take a look at my whole thing - maybe I didn't reproduce your thing the right way.

MrExcelPlayground19.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Sample Data								Formula Data
2		Order#	Date	No	Error Code	Old Price	New Price	User		Order#	Date	Old Price	New Price	User	Number of Times
3	Err_1944	512878	1/1/2023	1	Err_1944	200	300	Atlanta	Atlanta	971110	1/8/2023	900	10	Frank	2
4		512878	1/4/2023	2	Err_1832	100	105	Boston		428753	1/10/2023	899	399	Henry	1
5		654888	1/5/2023	3	Err_0782	400	200	Colton		971110	1/9/2023	1000	500	Frank	2
6		654888	1/6/2023	4	Err_1480	600	800	Delta
7		971110	1/7/2023	5	Err_1832	50	100	Echo
8		971110	1/8/2023	6	Err_1944	900	10	Frank
9		971110	1/9/2023	7	Err_1480	1090	350	George
10		428753	1/10/2023	8	Err_1944	899	399	Henry
11		971110	1/9/2023	6	Err_1944	1000	500	Frank
Sheet30

Cell Formulas
Range	Formula
J3:N5	J3	=FILTER(CHOOSECOLS(B3:H99,1,2,5,6,7),(E3:E99=A3)*(H3:H99<>I3))
O3:O5	O3	=COUNTIFS(B3:B100,TAKE(J3#,,1),E3:E100,A3,H3:H100,"<>"&I3)
Dynamic array formulas.

 

[Anonnymouse]

Thank you James.

It worked!