GOOGLE SHEETS: Counting Unique values based on another cells data.

[j3sharp]

I keep a running total of how many orders need to be short shipped. I am able to use (=COUNTIFS(B:B,">=10/1/2022",B:B,"<=10/08/2022") to count the number of lines in a given time period as each line is required to be put in by itself. I would like to keep track of the MRs in the same way but an MR# can be added multiple time for different lines. How could I go about making a formula that would count the number of Unique MR#'s in column A based on a date range in Column B. I am not able to change the formatting of the sheet or add other lines as this is on Google sheets and is used by multiple people in the company.

Distressed MRs (3).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	MR	Date Added	Line #	Distressed Part Code	QTY	Containers (list all unless pooling)	BU	Short Shipped / Revised		Month	Week	Order / Line	Orders	Short Shipped / Revised	%
2	MR02227481	10/3/2022	21	6548	2	N/A	NTNEE	Revised		Oct	Week 1	Orders	252		0.00%
3	MR02228201	10/3/2022	13	407998	13	VZC05116246	NTMIK	Short Shipped				Lines	2051	16	0.78%
4	MR02228796	10/3/2022	1	MX06FIT	2		NTWAB	Short Shipped			Week 2	Orders	1558		0.00%
5	MR02228465	10/3/2022	2	GPS-26NCM	1	VZC04043591	NTWAB	Short Shipped				Lines	12066	5	0.04%
6	MR02228465	10/3/2022	3	10915	1	VZC04043591	NTWAB	Short Shipped			Week 3	Orders			#DIV/0!
7	MR02227916	10/3/2022	2	U-3	1	VZC04803628	NTUNY	Revised				Lines		0	#DIV/0!
8	MR02227916	10/3/2022	21	HFT410-3J1-30	2	VZC04705188	NTUNY	Revised			Week 4	Orders			#DIV/0!
9	MR02195808	10/3/2022	32	SLS-B0EZEX	1	VZC05167293	NTNYM	Revised				Lines		0	#DIV/0!
10	MR02228390	10/3/2022	1	ESJ	2	VZC05116330	NTWAB	Short Shipped			Week 5	Orders			#DIV/0!
11	MR02227544	10/3/2022	7	RCS-5	3	VZC05035434	NTWAB	Revised				Lines		0	#DIV/0!
12	MR02228428	10/3/2022	5	RCS-5	3	VZC05035434	NTWAB	Revised			Total	Orders			#DIV/0!
13	MR02226805	10/3/2022	30	NW-FMSE	3	VZC05045610	NTPWO	Revised				Lines		21	#DIV/0!
14	MR02228397	10/3/2022	1	849143	1	VZC02810487	NTPWO	Short Shipped
15	MR02228397	10/3/2022	9	300297	1	VZC03037598	NTPWO	Short Shipped
16	MR02228397	10/3/2022	10	3002	1	VZC00047201	NTNYM	Short Shipped
17	MR02228096	10/3/2022	15	SMALL-C	12	VZC049866323	NTUNY	Revised
18	MR02230272	10/10/2022	6	VRAN-V	1		NTPHI	Revised
19	MR02230811	10/10/2022	10	KRY10/2	1	VZC05090280	NTCAR	Revised
20	MR02230140	10/10/2022	38	NW-F301VZ	2	pooling	NTIPO	Short Shipped
21	MR02229328	10/10/2022	27	10918	1	pooling	NTIPO	Short Shipped
22	MR02232572	10/11/2022	23	3HE1AA	1	pooling	NTUNY	Short Shipped
Sheet2

Cell Formulas
Range	Formula
N3	N3	=COUNTIFS(B:B,">=10/1/2022",B:B,"<=10/08/2022")
N5	N5	=COUNTIFS(B:B,">=10/9/2022",B:B,"<=10/15/2022")
N7	N7	=COUNTIFS(B:B,">=10/16/2022",B:B,"<=10/22/2022")
G9	G9	=IFERROR(__xludf.DUMMYFUNCTION("if($A397="""","""",vlookup($A397,importrange(""https://docs.google.com/spreadsheets/d/1CarnJBaaenB9jArwN2pu_rgzCkDOnhZjGLmZfZVfZ8E/edit#gid=0&fvid=1831903662"",""Outbound Tracker!$A:$AA""),27,false))"),"NTNYM")
N9	N9	=COUNTIFS(B:B,">=10/23/2022",B:B,"<=10/29/2022")
N11	N11	=COUNTIFS(B:B,">=10/30/2022",B:B,"<=10/31/2022")
O2:O13	O2	=N2/M2
N13	N13	=N3+N5+N7+N9+N11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A22,C2:D22,F2:G22,E5:E22	Expression	=OR($W2="RESOLVED",$W2="CANCELLED")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H1:H22	List	='Color Key'!$F$43:$F$44

 

[etaf]

are you still using version 2013 ???

=SUMPRODUCT(IF((B2:B8<=E2)*(B2:B8>=E1), 1/COUNTIFS(B2:B8, "<="&E2, B2:B8, ">="&E1, A2:A8, A2:A8), 0))

Count Unique based on dates.xlsx
	A	B	C	D	E
1	Code	Startdate		start	8/24/22
2	------	-------------		end	8/26/22
3	a123	21-Aug-22
4	b124	25-Aug-22			Unique Count
5	b124	25-Aug-22			3
6	c127	26-Aug-22
7	c128	24-Aug-22
8	c128	24-Aug-22
Sheet1

Cell Formulas
Range	Formula
E5	E5	=SUMPRODUCT(IF((B2:B8<=E2)*(B2:B8>=E1), 1/COUNTIFS(B2:B8, "<="&E2, B2:B8, ">="&E1, A2:A8, A2:A8), 0))

 

[j3sharp]

are you still using version 2013 ???

=SUMPRODUCT(IF((B2:B8<=E2)*(B2:B8>=E1), 1/COUNTIFS(B2:B8, "<="&E2, B2:B8, ">="&E1, A2:A8, A2:A8), 0))

Count Unique based on dates.xlsx
	A	B	C	D	E
1	Code	Startdate		start	8/24/22
2	------	-------------		end	8/26/22
3	a123	21-Aug-22
4	b124	25-Aug-22			Unique Count
5	b124	25-Aug-22			3
6	c127	26-Aug-22
7	c128	24-Aug-22
8	c128	24-Aug-22
Sheet1

Cell Formulas
Range	Formula
E5	E5	=SUMPRODUCT(IF((B2:B8<=E2)*(B2:B8>=E1), 1/COUNTIFS(B2:B8, "<="&E2, B2:B8, ">="&E1, A2:A8, A2:A8), 0))

The work computer is using 2013, but the excell sheet is on google sheets

 

[Joe4]

but the excell sheet is on google sheets

What exactly do you mean by this?
Are you trying to do this in Excel, or in Google Sheets?

 

[j3sharp]

What exactly do you mean by this?
Are you trying to do this in Excel, or in Google Sheets?

The sheet is hosted on Google Sheets as it is used company wide. I am responsible for updating this section of the document. I often download the sheet to make edits so I am not stepping on anybody else trying to do work and replace the data on the online sheet when done. Sorry if I was not clear on that.

 

[Joe4]

Note that while there is commonality between Excel and Google Sheets, they are NOT the same program and do have a number of differences. So not all Excel solutions will work in Google Sheets.

As such, you should post all Google Sheets questions to this forum General Discussion & Other Applications, as per the description of this forum:

Also please be sure to clearly mention in the thread title and/or question that you are trying to do this in Google Sheets. This way people who are trying to help you know exactly what program you are working with, and won’t provide answers that might not work for you.

I have moved this thread for you and updated your thread title.

 

[j3sharp]

=SUMPRODUCT(IF((B2:B10000<=Z44)*(B2:B10000>=Y44), 1/COUNTIFS(B2:B10000, "<="&Z44, B2:B10000, ">="&Y44, A2:A10000, A2:A10000),0))