Need to match site code and pick the furthest date out

[rentonhighlands]

Need to match site code and pick the furthest date out

code date
site code 1 1/27/2025
site code 1 2/15/2025
site code 2 3/1/2025
site code 2 3/25/2025

 

[rentonhighlands]

=IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$1000,B2),WORKDAY(MAXIFS($Y$2:$Y$1000,$Y$2:$Y$1000,Y2),60),"")
This is the formula I have in my data

Where col Y in my data is blank I want the formula to return a blank

what is happening is where excel only find 1 of the same code in col B then it returns 3/23/1900. In other words where it does not find 2 of the same then it returns 3/23/1900.

 

[etaf]

ok, so if no value in code column OR no value in the DATE column
=IF(OR(A3="",B3=""),"",IF(COUNTIF($A$3:A3,A3)=COUNTIF($A$3:$A$1000,A3),WORKDAY(MAXIFS($B$3:$B$1000,$A$3:$A$1000,A3),60),""))

you are using
=IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$1000,B2),WORKDAY(MAXIFS($Y$2:$Y$1000,$Y$2:$Y$1000,Y2),60),"")
SO the code is in column B and the date is in columnY

so this should work
=IF(OR(B2="",Y2=""),"", IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$1000,B2),WORKDAY(MAXIFS($Y$2:$Y$1000,$Y$2:$Y$1000,Y2),60),""))

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N											Y	Z
1		code																							date	60 business days
2		site code 1																							1/27/25
3
4		site code 1																							11/15/25
5		site code 1																							3/1/25
6		site code 1																							4/1/25
7		site code 2																							3/1/25	5/23/25
8		site code 12																							3/25/25	6/17/25
9		Site code 3																							11/1/25
10		Site code 3																							4/25/25	1/23/26
11		site code 4																							5/1/25
12		site code 4																							5/25/25	8/15/25
13		site code 5																							3/1/25	5/23/25
14		site code 6																							3/15/25	6/6/25
15		site code 7																							3/16/25	6/6/25
16		site code 8																							3/17/25	6/9/25
17		site code 9																							3/18/25	6/10/25
18		site code 10																							3/19/25	6/11/25
19
20		code																							date	3/23/00
21		site code 1																							1/27/25
22		site code 1																							11/15/25	2/6/26
23		site code 200																							3/1/25	5/23/25
24		site code 201
25		site code 202
26		site code 203
27		site code 204
28		site code 205
29		site code 206
30		site code 207
31		site code 208
32		site code 209
33		site code 210
34
35
36
37
38
39
40
41
42
43
Sheet1

Cell Formulas
Range	Formula
Z2:Z43	Z2	=IF(OR(B2="",Y2=""),"",IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$1000,B2),WORKDAY(MAXIFS($Y$2:$Y$1000,$B$2:$B$1000,B2),60),""))

 

[rentonhighlands]

=IF(OR(B2="",Y2=""),"", IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$1000,B2),WORKDAY(MAXIFS($Y$2:$Y$1000,$Y$2:$Y$1000,Y2),60),""))
yes this does work however it does not capture all. There are dates in col Y and the formula is not seeing them or could there be a formatting issue

 

[rentonhighlands]

=IF(OR(B2="",Y2=""),"", IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$1000,B2),WORKDAY(MAXIFS($Y$2:$Y$1000,$Y$2:$Y$1000,Y2),60),""))
yes this does work however it does not capture all. There are dates in col Y and the formula is not seeing them or could there be a formatting issue

Hang on maybe it is working validating. Will update the thread tomorrow. Thank you

 

[etaf]

you may need to change the range , if its more than a 1000 rows you are referencing

 

[rentonhighlands]

=IF(OR(B2="",Y2=""),"", IF(COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$1000,B2),WORKDAY(MAXIFS($Y$2:$Y$1000,$Y$2:$Y$1000,Y2),60),""))
This is working sorta
How can I alter this formula to do this
Look down column A match site code, look at col e find "private", look at col F find the latest date and return 60 work days from latest date

Site Code A	Site Name A	Site Address A	Site City A	Type	Carrier Estimated Delivery Date	Estimated Disconnect Date
site code 1	Broadband
site code 1	Private	5/31/2025
site code 1	Internet	5/31/2025
site code 1	Private	5/31/2025
site code 1	Internet	5/31/2025	8/22/2025

 

[etaf]

its now in column A ??? rather than B
Col E - look for private is that Site Name A
and then col F

confused with your layout and question - see my post 12 , using Y for date , hence the formula references

can you post an XL2BB - so the data is correctly formatted
OR
share a file

also provide expected results ? would help

i suspect a countifs may work

 

[rentonhighlands]

its now in column A ??? rather than B
Col E - look for private is that Site Name A
and then col F

confused with your layout and question - see my post 12 , using Y for date , hence the formula references

can you post an XL2BB - so the data is correctly formatted
OR
share a file

also provide expected results ? would help

i suspect a countifs may work

 

[rentonhighlands]

I can't download xls2bb or upload a file. How do I upload a file?

 

[etaf]

places like dropbox , onedrive , you can then share the file and post the link here
or googlesheets , and again share for everyone and then post the link here

just a guess
=IF(OR(A2="",Y2=""),"", IF(COUNTIFS($A$2:A2,A2,$E$2:E2,"private" )=COUNTIF($A$2:$A$1000,A2 , $E$2:$E$1000,"private" ),WORKDAY(MAXIFS($Y$2:$Y$1000,$Y$2:$Y$1000,Y2),60),""))