How to count all the headlines listed in column CT between dates of July 1 and July 31 2019 listed in column CT

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile
Hello. Asking for your help to create a formula to count all the items listed in column CT between dates of July 1 and July 31 2019 listed in column DE in this example. Thank you in advance for your help.

2019-22 Media Clips v06.xlsx
CTCUCVCWCXCYCZDADBDCDDDE
1171 HeadlinesDistributed by 167 OutletsAcross 1 Direct Media AreasDma RankAcross 135 Citiesand 34 Statesand 1 CountriesProduced a combined total of 105,537,593 Circulation, Impressions, and Event AttendeesWith a total of $6,124,953 in Media ValueConsisting of 57 Print Articles, 285 Web Headlines, and Attendees to 0 Events that breaks down to:Published DatePosted Date
2Englewood Community News Briefs for July 2, 2019Sun NewspapersN/AN/AENGLEWOODFLUSA21,268$485 - Web07-01-20197/3/2019
3Promoting safe boating SMITH MOUNTAIN EAGLEN/A68MONETAVAUSA4,400$166 - Print06-26-20197/10/2019
4Root Beer Floats", but you don't, so wear your life jacket BEDFORD BULLETINN/A68BEDFORDVAUSA4,563$276 - Print06-26-20197/10/2019
5New life jacket loaner stands open in Norwalk - The Register CitizenRegister CitizenN/AN/ATORRINGTONCTUSA17,253$364 - Web07-12-20197/18/2019
6New life jacket loaner stands open in NorwalkConnecticut PostN/AN/ABRIDGEPORTCTUSA394,799$13,453 - Web07-12-20197/18/2019
7New life jacket loaner stands open in NorwalkMiddletown PressN/AN/AMIDDLETOWNCTUSA11,272$95 - Web07-12-20197/18/2019
8'ABCs' are important in drowning preventionFort Myers Beach ObserverN/AN/AFORT MYERSFLUSA3,738$1,205 - Web07-19-20197/24/2019
9Water safety: 'ABCs' are important in drowning preventionIsland ReporterN/AN/ASANIBELFLUSA507$9,563 - Web07-19-20197/24/2019
10Water safety: 'ABCs' are important in drowning preventionCape Coral Daily BreezeN/AN/ACAPE CORALFLUSA2,878$32 - Web07-18-20197/24/2019
11Wake the World returns to Crazy Horse Marina ROANOKE TIMESN/A68ROANOKEVAUSA35,946$1,159 - Print07-31-20198/1/2019
12Ten tips to make boating with children safe, fun FLORIDA WEEKLYN/AN/AFORT MYERSFLUSA18,242$400 - Print07-30-20198/7/2019
13Sea Tow Foundation Receives US Coast Guard GrantsMarina Dock AgeN/AN/ANILESILUSA8,500$447 - Web08-05-20198/9/2019
14 Sea Tow Foundation to present safety awards at Boating Industry's Elevate - Boating IndustryBoating Industry OnlineN/AN/AMAPLE GROVEMNUSA9,758$75 - Web08-15-20198/15/2019
15 Sea Tow Foundation launches new awards - Trade Only TodaySoundings: Trade OnlyN/AN/AESSEXCTUSA12,219$97 - Web08-15-20198/19/2019
16 Sea Tow Foundation Extends Deadline for Boating Industry Safety AwardsFishing Wire (The)N/AN/AGLEN ALLENVAUSA2,946$29 - Web09-24-20199/30/2019
17Coast Guard Awards Grants to Sea Tow Foundation Programs LAKELAND BOATINGN/AN/AEVANSTONILUSA42,096$642 - Print10-01-201910/21/2019
18 Sea Tow Foundation Announces National Boating Industry Safety Award WinnersPublicN/AN/ANEW YORKNYUSA18,219$63 - Web12-04-201912/4/2019
19Sea Ray and Freedom Boat Club win National Safety AwardsCount on 2 FirstN/AN/ANAPLESFLUSA69,903$1,052 - Web12-02-201912/4/2019
20Downriver communities wrap up yearVoice (The)N/AN/ANEW BALTIMOREMIUSA2,510$152 - Web12-19-201912/23/2019
21Hartford Boat Show returns to Mohegan Sun - New Haven RegisterNew Haven RegisterN/AN/ANEW HAVENCTUSA111,844$3,581 - Web01-08-20201/9/2020
22With Flying Colors - Soundings OnlineSoundingsN/AN/AESSEXCTUSA21,182$348 - Web01-15-20201/16/2020
23Enhance Your Customers' Boating Experience with Peace of Mind on the Water SOUNDINGS (TRADE ONLY)N/AN/AESSEXCTUSA27,272$2,718 - Print04-01-20203/31/2020
24 Sea Tow Foundation Launches New WebsiteOutdoor Wire (The)N/AN/AGLEN ALLENVAUSA520$22 - Web04-03-20204/6/2020
25Mom Honors Her Late Son With Hernando Beach Life Jacket Loaner StationSpectrum Bay News 9N/AN/ATAMPAFLUSA230,161$2,621 - Web05-17-20205/18/2020
2610 tips for boating with kids by Safe Kids, Heather O'BrienSWFL Parent & ChildN/AN/AFORT MYERSFLUSA8,500$934 - Web07-02-20197/3/2019
raw
Cell Formulas
RangeFormula
CT1CT1=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(CT2:CT492)>0,MATCH(CT2:CT492,CT2:CT492,0),""),IF(LEN(CT2:CT492)>0,MATCH(CT2:CT492,CT2:CT492,0),""))>0,1))," Headlines")
CU1CU1=CONCATENATE("Distributed by ",SUM(IF(FREQUENCY(IF(LEN(CU2:CU492)>0,MATCH(CU2:CU492,CU2:CU492,0),""),IF(LEN(CU2:CU492)>0,MATCH(CU2:CU492,CU2:CU492,0),""))>0,1))," Outlets")
CV1CV1=CONCATENATE("Across ",SUM(IF(FREQUENCY(IF(LEN(CV2:CV492)>0,MATCH(CV2:CV492,CV2:CV492,0),""),IF(LEN(CV2:CV492)>0,MATCH(CV2:CV492,CV2:CV492,0),""))>0,1))," Direct Media Areas")
CX1CX1=CONCATENATE("Across ",SUM(IF(FREQUENCY(IF(LEN(CX2:CX492)>0,MATCH(CX2:CX492,CX2:CX492,0),""),IF(LEN(CX2:CX492)>0,MATCH(CX2:CX492,CX2:CX492,0),""))>0,1))," Cities")
CY1CY1=CONCATENATE("and ",SUM(IF(FREQUENCY(IF(LEN(CY2:CY492)>0,MATCH(CY2:CY492,CY2:CY492,0),""),IF(LEN(CY2:CY492)>0,MATCH(CY2:CY492,CY2:CY492,0),""))>0,1))," States")
CZ1CZ1=CONCATENATE("and ",SUM(IF(FREQUENCY(IF(LEN(CZ2:CZ492)>0,MATCH(CZ2:CZ492,CZ2:CZ492,0),""),IF(LEN(CZ2:CZ492)>0,MATCH(CZ2:CZ492,CZ2:CZ492,0),""))>0,1))," Countries")
DA1DA1=CONCATENATE("Produced a combined total of ",TEXT(SUM(DA2:DA492),"#,##0")," Circulation, Impressions, and Event Attendees")
DB1DB1=CONCATENATE("With a total of $",TEXT(SUM(DB2:DB492),"#,##0")," in Media Value")
DC1DC1=CONCATENATE("Consisting of ",COUNTIF(DC2:DC492,"*Print*")," Print Articles, ",COUNTIF(DC2:DC492,"*Web*")," Web Headlines, and Attendees to ",COUNTIF(DC2:DC492,"*Event*")," Events that breaks down to:")
 

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile
Hello. I am asking for your help with a discrepancy that has appeared in the solution identified for this post and asking for your help to identify and fix it- please see X3, X5-7 in this example. The answer should be zero and not 1 because the date column the formula is pulling from does not contain 2021 dates. Thank you in advance for your help with this.

Cell Formulas
RangeFormula
B3B3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2019,1,1))*(raw!$DN$2:$DN$1000<=DATE(2020,12,31)),"")))
C3C3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2019,1,1))*(raw!$DN$2:$DN$1000<=DATE(2019,12,31)),"")))
D3D3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,1,1))*(raw!$DN$2:$DN$1000<=DATE(2020,12,31)),"")))
B4,B8:B9B4=SUM(C4:E4)
C4,C8:C9C4=SUM(F4:K4)
D4,D8:D9D4=SUM(L4:W4)
B5B5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2019,1,1))*(raw!$DN$2:$DN$1001<=DATE(2020,12,31)),"")))
C5C5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2019,1,1))*(raw!$DN$2:$DN$1001<=DATE(2019,12,31)),"")))
D5D5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,1,1))*(raw!$DN$2:$DN$1001<=DATE(2020,12,31)),"")))
B6B6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2019,1,1))*(raw!$DN$2:$DN$1001<=DATE(2020,12,31)),"")))
C6C6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2019,1,1))*(raw!$DN$2:$DN$1001<=DATE(2019,12,31)),"")))
D6D6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,1,1))*(raw!$DN$2:$DN$1001<=DATE(2020,12,31)),"")))
B7B7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2019,1,1))*(raw!$DN$2:$DN$1001<=DATE(2020,12,31)),"")))
C7C7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2019,1,1))*(raw!$DN$2:$DN$1001<=DATE(2019,12,31)),"")))
D7D7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,1,1))*(raw!$DN$2:$DN$1001<=DATE(2020,12,31)),"")))
F3F3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2019,7,1))*(raw!$DN$2:$DN$1000<=DATE(2019,7,31)),"")))
G3G3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2019,8,1))*(raw!$DN$2:$DN$1000<=DATE(2019,8,31)),"")))
H3H3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2019,9,1))*(raw!$DN$2:$DN$1000<=DATE(2019,9,30)),"")))
I3I3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2019,10,1))*(raw!$DN$2:$DN$1000<=DATE(2019,10,31)),"")))
J3J3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2019,11,1))*(raw!$DN$2:$DN$1000<=DATE(2019,11,30)),"")))
K3K3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2019,12,1))*(raw!$DN$2:$DN$1000<=DATE(2019,12,31)),"")))
L3L3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,1,1))*(raw!$DN$2:$DN$1000<=DATE(2020,1,31)),"")))
M3M3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,2,1))*(raw!$DN$2:$DN$1000<=DATE(2020,2,29)),"")))
N3N3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,3,1))*(raw!$DN$2:$DN$1000<=DATE(2020,3,31)),"")))
O3O3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,4,1))*(raw!$DN$2:$DN$1000<=DATE(2020,4,30)),"")))
P3P3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,5,1))*(raw!$DN$2:$DN$1000<=DATE(2020,5,31)),"")))
Q3Q3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,6,1))*(raw!$DN$2:$DN$1000<=DATE(2020,6,30)),"")))
R3R3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,7,1))*(raw!$DN$2:$DN$1000<=DATE(2020,7,31)),"")))
S3S3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,8,1))*(raw!$DN$2:$DN$1000<=DATE(2020,8,31)),"")))
T3T3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,9,1))*(raw!$DN$2:$DN$1000<=DATE(2020,9,30)),"")))
U3U3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,10,1))*(raw!$DN$2:$DN$1000<=DATE(2020,10,31)),"")))
V3V3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,11,1))*(raw!$DN$2:$DN$1000<=DATE(2020,11,30)),"")))
W3W3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2020,12,1))*(raw!$DN$2:$DN$1000<=DATE(2020,12,31)),"")))
X3X3=COUNTA(UNIQUE(FILTER(raw!$DC$2:$DC$1000,(raw!$DN$2:$DN$1000>=DATE(2021,1,1))*(raw!$DN$2:$DN$1000<=DATE(2021,1,31)),"")))
F4F4=COUNTIFS(raw!$DN2:$DN1000,">=7/1/2019",raw!$DN2:$DN$1000,"<=7/31/2019")
G4G4=COUNTIFS(raw!$DN2:$DN1000,">=8/1/2019",raw!$DN2:$DN$1000,"<=8/31/2019")
H4H4=COUNTIFS(raw!$DN2:$DN1000,">=9/1/2019",raw!$DN2:$DN$1000,"<=9/30/2019")
I4I4=COUNTIFS(raw!$DN2:$DN1000,">=10/1/2019",raw!$DN2:$DN$1000,"<=10/31/2019")
J4J4=COUNTIFS(raw!$DN2:$DN1000,">=11/1/2019",raw!$DN2:$DN$1000,"<=11/30/2019")
K4K4=COUNTIFS(raw!$DN2:$DN1000,">=12/1/2019",raw!$DN2:$DN$1000,"<=12/31/2019")
L4L4=COUNTIFS(raw!$DN2:$DN1000,">=1/1/2020",raw!$DN2:$DN$1000,"<=1/31/2020")
M4M4=COUNTIFS(raw!$DN2:$DN1000,">=2/1/2020",raw!$DN2:$DN$1000,"<=2/29/2020")
N4N4=COUNTIFS(raw!$DN2:$DN1000,">=3/1/2020",raw!$DN2:$DN$1000,"<=3/31/2020")
O4O4=COUNTIFS(raw!$DN2:$DN1000,">=4/1/2020",raw!$DN2:$DN$1000,"<=4/30/2020")
P4P4=COUNTIFS(raw!$DN2:$DN1000,">=5/1/2020",raw!$DN2:$DN$1000,"<=5/31/2020")
Q4Q4=COUNTIFS(raw!$DN2:$DN1000,">=6/1/2020",raw!$DN2:$DN$1000,"<=6/30/2020")
R4R4=COUNTIFS(raw!$DN2:$DN1000,">=7/1/2020",raw!$DN2:$DN$1000,"<=7/31/2020")
S4S4=COUNTIFS(raw!$DN2:$DN1000,">=8/1/2020",raw!$DN2:$DN$1000,"<=8/31/2020")
T4T4=COUNTIFS(raw!$DN2:$DN1000,">=9/1/2020",raw!$DN2:$DN$1000,"<=9/30/2020")
U4U4=COUNTIFS(raw!$DN2:$DN1000,">=10/1/2020",raw!$DN2:$DN$1000,"<=10/31/2020")
V4V4=COUNTIFS(raw!$DN2:$DN1000,">=11/1/2020",raw!$DN2:$DN$1000,"<=11/30/2020")
W4W4=COUNTIFS(raw!$DN2:$DN1000,">=12/1/2020",raw!$DN2:$DN$1000,"<=12/31/2020")
X4X4=COUNTIFS(raw!$DN2:$DN1000,">=1/1/2021",raw!$DN2:$DN$1000,"<=1/31/2021")
F5F5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2019,7,1))*(raw!$DN$2:$DN$1001<=DATE(2019,7,31)),"")))
G5G5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2019,8,1))*(raw!$DN$2:$DN$1001<=DATE(2019,8,31)),"")))
H5H5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2019,9,1))*(raw!$DN$2:$DN$1001<=DATE(2019,9,30)),"")))
I5I5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2019,10,1))*(raw!$DN$2:$DN$1001<=DATE(2019,10,31)),"")))
J5J5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2019,11,1))*(raw!$DN$2:$DN$1001<=DATE(2019,11,3)),"")))
K5K5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2019,12,1))*(raw!$DN$2:$DN$1001<=DATE(2019,12,31)),"")))
L5L5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,1,1))*(raw!$DN$2:$DN$1001<=DATE(2020,1,31)),"")))
M5M5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,2,1))*(raw!$DN$2:$DN$1001<=DATE(2020,2,29)),"")))
N5N5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,3,1))*(raw!$DN$2:$DN$1001<=DATE(2020,3,31)),"")))
O5O5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,4,1))*(raw!$DN$2:$DN$1001<=DATE(2020,4,30)),"")))
P5P5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,5,1))*(raw!$DN$2:$DN$1001<=DATE(2020,5,31)),"")))
Q5Q5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,6,1))*(raw!$DN$2:$DN$1001<=DATE(2020,6,30)),"")))
R5R5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,7,1))*(raw!$DN$2:$DN$1001<=DATE(2020,7,31)),"")))
S5S5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,8,1))*(raw!$DN$2:$DN$1001<=DATE(2020,8,31)),"")))
T5T5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,9,1))*(raw!$DN$2:$DN$1001<=DATE(2020,9,30)),"")))
U5U5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,10,1))*(raw!$DN$2:$DN$1001<=DATE(2020,10,31)),"")))
V5V5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,11,1))*(raw!$DN$2:$DN$1001<=DATE(2020,11,30)),"")))
W5W5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2020,12,1))*(raw!$DN$2:$DN$1001<=DATE(2020,12,31)),"")))
X5X5=COUNTA(UNIQUE(FILTER(raw!$DD$2:$DD$1001,(raw!$DN$2:$DN$1001>=DATE(2021,1,1))*(raw!$DN$2:$DN$1000<=DATE(2021,1,31)),"")))
F6F6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2019,7,1))*(raw!$DN$2:$DN$1001<=DATE(2019,7,31)),"")))
G6G6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2019,8,1))*(raw!$DN$2:$DN$1001<=DATE(2019,8,31)),"")))
H6H6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2019,9,1))*(raw!$DN$2:$DN$1001<=DATE(2019,9,30)),"")))
I6I6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2019,10,1))*(raw!$DN$2:$DN$1001<=DATE(2019,10,31)),"")))
J6J6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2019,11,1))*(raw!$DN$2:$DN$1001<=DATE(2019,11,30)),"")))
K6K6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2019,12,1))*(raw!$DN$2:$DN$1001<=DATE(2019,12,31)),"")))
L6L6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,1,1))*(raw!$DN$2:$DN$1001<=DATE(2020,1,31)),"")))
M6M6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,2,1))*(raw!$DN$2:$DN$1001<=DATE(2020,2,29)),"")))
N6N6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,3,1))*(raw!$DN$2:$DN$1001<=DATE(2020,3,31)),"")))
O6O6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,4,1))*(raw!$DN$2:$DN$1001<=DATE(2020,4,30)),"")))
P6P6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,5,1))*(raw!$DN$2:$DN$1001<=DATE(2020,5,31)),"")))
Q6Q6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,6,1))*(raw!$DN$2:$DN$1001<=DATE(2020,6,30)),"")))
R6R6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,7,1))*(raw!$DN$2:$DN$1001<=DATE(2020,7,31)),"")))
S6S6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,8,1))*(raw!$DN$2:$DN$1001<=DATE(2020,8,31)),"")))
T6T6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,9,1))*(raw!$DN$2:$DN$1001<=DATE(2020,9,3)),"")))
U6U6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,10,1))*(raw!$DN$2:$DN$1001<=DATE(2020,10,31)),"")))
V6V6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,11,1))*(raw!$DN$2:$DN$1001<=DATE(2020,11,30)),"")))
W6W6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2020,12,1))*(raw!$DN$2:$DN$1001<=DATE(2020,12,31)),"")))
X6X6=COUNTA(UNIQUE(FILTER(raw!$DG$2:$DG$1001,(raw!$DN$2:$DN$1001>=DATE(2021,1,1))*(raw!$DN$2:$DN$1001<=DATE(2021,1,31)),"")))
F7F7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2019,7,1))*(raw!$DN$2:$DN$1001<=DATE(2019,7,31)),"")))
G7G7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2019,8,1))*(raw!$DN$2:$DN$1001<=DATE(2019,8,31)),"")))
H7H7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2019,9,1))*(raw!$DN$2:$DN$1001<=DATE(2019,9,30)),"")))
I7I7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2019,10,1))*(raw!$DN$2:$DN$1001<=DATE(2019,10,31)),"")))
J7J7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2019,11,1))*(raw!$DN$2:$DN$1001<=DATE(2019,11,30)),"")))
K7K7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2019,12,1))*(raw!$DN$2:$DN$1001<=DATE(2019,12,31)),"")))
L7L7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,1,1))*(raw!$DN$2:$DN$1001<=DATE(2020,1,31)),"")))
M7M7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,2,1))*(raw!$DN$2:$DN$1001<=DATE(2020,2,29)),"")))
N7N7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,3,1))*(raw!$DN$2:$DN$1001<=DATE(2020,3,31)),"")))
O7O7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,4,1))*(raw!$DN$2:$DN$1001<=DATE(2020,4,30)),"")))
P7P7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,5,1))*(raw!$DN$2:$DN$1001<=DATE(2020,5,31)),"")))
Q7Q7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,6,1))*(raw!$DN$2:$DN$1001<=DATE(2020,6,30)),"")))
R7R7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,7,1))*(raw!$DN$2:$DN$1001<=DATE(2020,7,31)),"")))
S7S7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,8,1))*(raw!$DN$2:$DN$1001<=DATE(2020,8,31)),"")))
T7T7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,9,1))*(raw!$DN$2:$DN$1001<=DATE(2020,9,30)),"")))
U7U7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,10,1))*(raw!$DN$2:$DN$1001<=DATE(2020,10,31)),"")))
V7V7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,11,1))*(raw!$DN$2:$DN$1001<=DATE(2020,11,30)),"")))
W7W7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2020,12,1))*(raw!$DN$2:$DN$1001<=DATE(2020,12,31)),"")))
X7X7=COUNTA(UNIQUE(FILTER(raw!$DH$2:$DH$1001,(raw!$DN$2:$DN$1001>=DATE(2021,1,1))*(raw!$DN$2:$DN$1001<=DATE(2021,1,31)),"")))
F8F8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("7/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("7/31/2019"))
G8G8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("8/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("8/31/2019"))
H8H8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("9/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("9/30/2019"))
I8I8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("10/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("10/31/2019"))
J8J8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("11/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("11/30/2019"))
K8K8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("12/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("12/31/2019"))
L8L8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("1/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("1/31/2020"))
M8M8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("2/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("2/29/2020"))
N8N8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("3/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("3/31/2020"))
O8O8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("4/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("4/30/2020"))
P8P8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("5/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("5/31/2020"))
Q8Q8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("6/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("6/30/2020"))
R8R8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("7/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("7/31/2020"))
S8S8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("8/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("8/31/2020"))
T8T8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("9/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("9/30/2020"))
U8U8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("10/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("10/31/2020"))
V8V8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("11/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("11/30/2020"))
W8W8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("12/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("12/31/2020"))
X8X8=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DN$2:$DN$980,">"&DATEVALUE("1/1/2021"),raw!$DN$2:$DN$980,"<"&DATEVALUE("1/31/2021"))
F9F9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("7/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("7/31/2019"))
G9G9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("8/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("8/31/2019"))
H9H9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("9/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("9/30/2019"))
I9I9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("10/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("10/31/2019"))
J9J9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("11/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("11/30/2019"))
K9K9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("12/1/2019"),raw!$DN$2:$DN$980,"<"&DATEVALUE("12/31/2019"))
L9L9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("1/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("1/31/2020"))
M9M9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("2/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("2/29/2020"))
N9N9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("3/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("3/31/2020"))
O9O9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("4/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("4/30/2020"))
P9P9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("5/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("5/31/2020"))
Q9Q9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("6/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("6/30/2020"))
R9R9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("7/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("7/31/2020"))
S9S9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("8/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("8/31/2020"))
T9T9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("9/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("9/30/2020"))
U9U9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("10/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("10/31/2020"))
V9V9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("11/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("11/30/2020"))
W9W9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("12/1/2020"),raw!$DN$2:$DN$980,"<"&DATEVALUE("12/31/2020"))
X9X9=SUMIFS(raw!$DK$2:$DK$980,raw!$DN$2:$DN$980,">"&DATEVALUE("1/1/2021"),raw!$DN$2:$DN$980,"<"&DATEVALUE("1/31/2021"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
X3:X19Expression=MOD(ROW(),2)=0textNO
A38:W52,A54:W120,Y54:AS120,Y38:AS52,Y3:AS19,A3:W19Expression=MOD(ROW(),2)=0textNO
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top