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:")
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Give this a try: =COUNTIFS(CT:CT,">="&DATE(2019,7,1),CT:CT,"<="&DATE(2019,7,31))
 

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile
Give this a try: =COUNTIFS(CT:CT,">="&DATE(2019,7,1),CT:CT,"<="&DATE(2019,7,31))
Hello, I entered incorrect information in the headline and there is no way to go back and correct it - the question I am asking is How to count all the headlines listed in column CT between dates of July 1 and July 31 2019 listed in column DE
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
=COUNTIFS(DE:DE,">=7/1/2019",DE:DE,"<=7/31/2019")
 
Solution

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

=COUNTIFS(DE:DE,">=7/1/2019",DE:DE,"<=7/31/2019")
Sorry for the confusion but I am trying to count the headlines listed in CT between July 1, 2019 and July 31, 2019 in DE
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Have you actually tried the formula?
 

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Have you actually tried the formula?
Correct. I cannot make the formula count the headlines in column CT based on dates in column DE in the XL2BB example in the thread
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't it work?
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Regardless of what is in CT, what you're asking is how many values in DE are between the two dates. That is precisely what this formula does.
 

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile
Hello. I was able to get the formula to work- Thank you! Here is how it is applied:
Cell Formulas
RangeFormula
B3B3=COUNTIFS(raw!$DF2:$DF1000,">=7/1/2019",raw!$DF2:$DF$1000,"<=7/31/2019")
C3C3=COUNTIFS(raw!$DF2:$DF1000,">=8/1/2019",raw!$DF2:$DF$1000,"<=8/31/2019")
D3D3=COUNTIFS(raw!$DF2:$DF1000,">=9/1/2019",raw!$DF2:$DF$1000,"<=9/30/2019")
E3E3=COUNTIFS(raw!$DF2:$DF1000,">=10/1/2019",raw!$DF2:$DF$1000,"<=10/31/2019")
F3F3=COUNTIFS(raw!$DF2:$DF1000,">=11/1/2019",raw!$DF2:$DF$1000,"<=11/30/2019")
G3G3=COUNTIFS(raw!$DF2:$DF1000,">=12/1/2019",raw!$DF2:$DF$1000,"<=12/31/2019")
H3H3=COUNTIFS(raw!$DF2:$DF1000,">=1/1/2020",raw!$DF2:$DF$1000,"<=1/31/2020")
I3I3=COUNTIFS(raw!$DF2:$DF1000,">=2/1/2020",raw!$DF2:$DF$1000,"<=2/29/2020")
J3J3=COUNTIFS(raw!$DF2:$DF1000,">=3/1/2020",raw!$DF2:$DF$1000,"<=3/31/2020")
K3K3=COUNTIFS(raw!$DF2:$DF1000,">=4/1/2020",raw!$DF2:$DF$1000,"<=4/30/2020")
L3L3=COUNTIFS(raw!$DF2:$DF1000,">=5/1/2020",raw!$DF2:$DF$1000,"<=5/31/2020")
M3M3=COUNTIFS(raw!$DF2:$DF1000,">=6/1/2020",raw!$DF2:$DF$1000,"<=6/30/2020")
N3N3=COUNTIFS(raw!$DF2:$DF1000,">=7/1/2020",raw!$DF2:$DF$1000,"<=7/31/2020")
O3O3=COUNTIFS(raw!$DF2:$DF1000,">=8/1/2020",raw!$DF2:$DF$1000,"<=8/31/2020")
P3P3=COUNTIFS(raw!$DF2:$DF1000,">=9/1/2020",raw!$DF2:$DF$1000,"<=9/30/2020")
Q3Q3=COUNTIFS(raw!$DF2:$DF1000,">=10/1/2020",raw!$DF2:$DF$1000,"<=10/31/2020")
R3R3=COUNTIFS(raw!$DF2:$DF1000,">=11/1/2020",raw!$DF2:$DF$1000,"<=11/30/2020")
S3S3=COUNTIFS(raw!$DF2:$DF1000,">=12/1/2020",raw!$DF2:$DF$1000,"<=12/31/2020")
T3T3=COUNTIFS(raw!$DF2:$DF1000,">=1/1/2021",raw!$DF2:$DF$1000,"<=1/31/2021")
B7B7=SUMIFS(raw!$DB$2:$DB$980,raw!$DF$2:$DF$980,">"&DATEVALUE("7/1/2019"),raw!$DF$2:$DF$980,"<"&DATEVALUE("7/31/2019"))
B8B8=SUMIFS(raw!$DC$2:$DC$980,raw!$DF$2:$DF$980,">"&DATEVALUE("7/1/2019"),raw!$DF$2:$DF$980,"<"&DATEVALUE("7/31/2019"))
B10B10=SUMIFS(raw!$DH$2:$DH$980,raw!$DF$2:$DF$980,">"&DATEVALUE("7/1/2019"),raw!$DF$2:$DF$980,"<"&DATEVALUE("7/31/2019"))
B11B11=SUMIFS(raw!$DI$2:$DI$980,raw!$DF$2:$DF$980,">"&DATEVALUE("7/1/2019"),raw!$DF$2:$DF$980,"<"&DATEVALUE("7/31/2019"))
B12B12=SUMIFS(raw!$DJ$2:$DJ$980,raw!$DF$2:$DF$980,">"&DATEVALUE("7/1/2019"),raw!$DF$2:$DF$980,"<"&DATEVALUE("7/31/2019"))
B13B13=SUMIFS(raw!$DK$2:$DK$980,raw!$DF$2:$DF$980,">"&DATEVALUE("7/1/2019"),raw!$DF$2:$DF$980,"<"&DATEVALUE("7/31/2019"))
A3A3=CONCATENATE(raw!CU1," generated ",raw!CQ1)
A4A4=raw!CV1
A5A5=raw!CY1
A6A6=raw!CZ1
A7A7=raw!DB1
A8A8=raw!DC1
A9A9=raw!DD1
A10A10=raw!DH1
A11A11=raw!DI1
A12A12=raw!DJ1
A13A13=raw!DK1
A14A14=raw!DL1
A15A15=raw!BH1
B15B15=SUMIFS(raw!$BI$2:$BI$980,raw!$DF$2:$DF$980,">"&DATEVALUE("7/1/2019"),raw!$DF$2:$DF$980,"<"&DATEVALUE("7/31/2019"))
A16A16=raw!BI1
B16B16=SUMIFS(raw!$BH$2:$BH$980,raw!$DF$2:$DF$980,">"&DATEVALUE("7/1/2019"),raw!$DF$2:$DF$980,"<"&DATEVALUE("7/31/2019"))
A17A17=raw!BW1
B17B17=SUMIFS(raw!$BW$2:$BW$980,raw!$DF$2:$DF$980,">"&DATEVALUE("7/1/2019"),raw!$DF$2:$DF$980,"<"&DATEVALUE("7/31/2019"))
A18A18=raw!BX1
B18B18=SUMIFS(raw!$BX$2:$BX$980,raw!$DF$2:$DF$980,">"&DATEVALUE("7/1/2019"),raw!$DF$2:$DF$980,"<"&DATEVALUE("7/31/2019"))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

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