Unique or Sumproduct or something else?

[Montanes]

So I have a lot of data and someone has changed how it is imported!

I needed to find how many different orders were taken by a salesperson in the month so ran this

COUNTIFS(Dels!$O:$O,"BOB",Dels!$C:$C,">=01/08/2023",Dels!$C:$C,"<=31/08/2023",Dels!$H:$H,">0.00")

which ran perfectly as all orders were delivered and had a delivery cost in column H. Given that the order was only delivered once, it produced the amount of orders that were placed.

Now someone has changed the data criteria and column H no longer shows a delivery cost, its just always 0.00!

Column A however may hold a solution.

You see, if a customer orders 3 different products it is shown over 3 different lines or 4 products over 4 lines etc hence why I counted the deliveries which only appeared once per order.

Column A does have a sales code but its the same code for all the lines within the order. So if a customer orders 3 products, the 3 lines have the same number in column A.

This means I could somehow replace the last part of my original formula for column H to count the numbers in column A but only count the duplicates once or remove the duplicates or something.

Jeez I hope that all makes sense!

Thank-you in advance for any assistance.

 

[Herakles]

So I have a lot of data and someone has changed how it is imported!

I needed to find how many different orders were taken by a salesperson in the month so ran this

COUNTIFS(Dels!$O:$O,"BOB",Dels!$C:$C,">=01/08/2023",Dels!$C:$C,"<=31/08/2023",Dels!$H:$H,">0.00")

which ran perfectly as all orders were delivered and had a delivery cost in column H. Given that the order was only delivered once, it produced the amount of orders that were placed.

Now someone has changed the data criteria and column H no longer shows a delivery cost, its just always 0.00!

Column A however may hold a solution.

You see, if a customer orders 3 different products it is shown over 3 different lines or 4 products over 4 lines etc hence why I counted the deliveries which only appeared once per order.

Column A does have a sales code but its the same code for all the lines within the order. So if a customer orders 3 products, the 3 lines have the same number in column A.

This means I could somehow replace the last part of my original formula for column H to count the numbers in column A but only count the duplicates once or remove the duplicates or something.

Jeez I hope that all makes sense!

Thank-you in advance for any assistance.

Can you use XL2BB to post the data in a mini-sheet please?

 

[Fluff]

How about

=ROWS(UNIQUE(FILTER(Dels!$A2:$A10000,(Dels!$O2:$O10000="BOB")*(Dels!$C2:$C10000>="01/08/2023")*(Dels!$C2:$C10000<="31/08/2023"))))

 

[Montanes]

How about

=ROWS(UNIQUE(FILTER(Dels!$A2:$A10000,(Dels!$O2:$O10000="BOB")*(Dels!$C2:$C10000>="01/08/2023")*(Dels!$C2:$C10000<="31/08/2023"))))

That returns #CALC! - empty arrays are not supported

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Montanes]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Document Number	Item No	Posting Date	Location	Quantity	Sales Amount	Cost Amount	Haulage	Gibberish	Source	Entry	Invoice No	Measure	Yet Another Code	Salesperson
2	D123456	Socks	05/07/2023	Manchester	5	£100.00	£12.50	£0.00	0	PDQ15689	123456	INV11111	EAC		LISA
3	D123456	Shoes	05/07/2023	Manchester	5	£500.00	£62.50	£0.00	0	PDQ15689	123457	INV11111	EAC		LISA
4	D123456	Laces	05/07/2023	Manchester	5	£50.00	£6.25	£0.00	0	PDQ15689	123458	INV11111	MET		LISA
5	D123457	Shoes	01/08/2023	London	1	£100.00	£12.50	£0.00	0	PDQ16589	123459	INV11112	EAC		BOB
6	D123457	Laces	01/08/2023	London	1	£10.00	£1.25	£0.00	0	PDQ16589	123460	INV11112	MET		BOB
7	D123458	Laces	02/08/2023	Birmingham	3	£30.00	£3.75	£0.00	3	PDQ15877	123461	INV11113	MET		PAULINE
8	D123459	Socks	02/08/2023	Birmingham	24	£480.00	£60.00	£0.00	1	PDQ25666	123462	INV11114	EAC		JOE
9	D123459	Shoes	02/08/2023	Birmingham	24	£2,400.00	£300.00	£0.00	1	PDQ25666	123463	INV11114	EAC		JOE
10	D123459	Laces	02/08/2023	Manchester	24	£240.00	£30.00	£0.00	1	PDQ25666	123464	INV11114	MET		JOE
11	D123459	Box	02/08/2023	Manchester	24	£120.00	£15.00	£0.00	1	PDQ25666	123465	INV11114	EAC		JOE
12	D123460	Shoes	14/08/2023	London	12	£1,200.00	£150.00	£0.00	2	PDQ14986	123466	INV11115	EAC		BOB
13	D123460	Laces	14/08/2023	London	12	£120.00	£15.00	£0.00	2	PDQ14986	123467	INV11115	MET		BOB
14	D123461	Laces	19/08/2023	London	5	£50.00	£6.25	£0.00	1	PDQ24444	123468	INV11116	MET		LISA
15	D123462	Shoes	28/08/2023	Manchester	3	£300.00	£37.50	£0.00	3	PDQ13985	123469	INV11117	EAC		JOE
16	D123462	Laces	28/08/2023	Manchester	3	£30.00	£3.75	£0.00	3	PDQ13985	123470	INV11117	MET		JOE
Dels

 

[Fluff]

Thanks for that, how about

=ROWS(UNIQUE(FILTER(Dels!$A2:$A10000,(Dels!$O2:$O10000="BOB")*(Dels!$C2:$C10000>=DATE(2023,8,1))*(Dels!$C2:$C10000<=DATE(2023,8,31)))))