Counting distinct rows based on multiple criteria without Array formulas

[wdee]

Hello!

This is my first post, but I'm a long time lurker and up until now I always found a solution on your forum but this time I'm really stuck.
So here is the problem.

I need to count the number of orders of some type (C or I) in an export file where I have multiple lines for every item on order.
I know how to do it with array function but it takes too much time to do it. Export is about 10000 rows at a time.... any workaround for this?

Problem1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Order number	Type of order	Fruit	QTY	Price	Amount
2	1	C	Apple	4	1,05	4,2				Type of order	Count
3	1	C	Banana	2	0,2	0,4				C	7
4	1	C	Mango	3	0,5	1,5				I	4
5	1	I	Strawberry	4	0,1	0,4
6	1	I	Apple	2	1,05	2,1
7	1	I	Banana	6	0,2	1,2
8	2	C	Apple	4	1,05	4,2
9	2	C	Banana	2	0,2	0,4
10	2	C	Mango	3	0,5	1,5
11	3	C	Strawberry	22	0,1	2,2
12	3	C	Apple	41	1,05	43,05
13	4	C	Banana	13	0,2	2,6
14	5	C	Strawberry	22	0,1	2,2
15	6	C	Apple	41	1,05	43,05
16	7	C	Banana	13	0,2	2,6
17	2	I	Apple	4	1,05	4,2
18	3	I	Banana	2	0,2	0,4
19	4	I	Mango	3	0,5	1,5
Problem

Cell Formulas
Range	Formula
K3:K4	K3	=SUM(IF((J3=Type_of_order),1/COUNTIFS(Type_of_order,J3,Order_number,Order_number)),0)
F2:F19	F2	=D2*E2
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Order_number	=Problem!$A$2:$A$1048576	K3:K4
Price	=Problem!$E$2:$E$1048576	F2
QTY	=Problem!$D$2:$D$1048576	F2
Type_of_order	=Problem!$B$2:$B$1048576	K3:K4

 

[Fluff]

The reason your formula is slow, is that you are looking at the entire column, you should never do that with array formulae.
Try limiting the the ranges to 15,000 rows.

 

[wdee]

The reason your formula is slow, is that you are looking at the entire column, you should never do that with array formulae.
Try limiting the the ranges to 15,000 rows.

Thank you!
I'm aware that my ranges are through the entire column, but it did not occur to me at all that could solve my problem. Instead, I was looking for a workaround.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[shaowu459]

I think power query is a good way. do you familiar with it?

 

[shaowu459]

 

[wdee]

Thank you, but I can't use power query because I have report sheet where all statistics are calculated...

 

[shaowu459]

Thank you, but I can't use power query because I have report sheet where all statistics are calculated...

You can right click on the result and select Refresh to update the result of PQ, do not need do all the procedures again.
If you do not consider PQ, may be you will also not consider Pivot Table. Considering the large data you have, may be helper columns will reduce the time of calculation.