One sheet A, I want to sum a range from sheet B only if between a date range also on sheet B

[wmichael]

Hello. I am not sure about using Countifs, it's the only one I could find that performs in the way I want.

COUNTIFS(Clips!M2:Clips!M560,">"&"12/1/2020",Clips!B2:Clips!B560,"<"&"12/30/2020")

I don't know how to include an example.

Thank you in advance for your help with this.

 

[etaf]

i changed my dates to UK
=COUNTIFS(Clips!M1:M558,">"&"1/12/2020",Clips!B1:B558,"<"&"30/12/2020")
works

 

[etaf]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		12/1/20											12/30/20
2		12/2/20											12/29/20
3		12/3/20											12/28/20
4		12/4/20											12/27/20
5		12/5/20											12/26/20
6		12/6/20											12/25/20
7		12/7/20											12/24/20
8		12/8/20											12/23/20
9		12/9/20											12/22/20
10		12/10/20											12/21/20
11		12/11/20											12/20/20
12		12/12/20											12/19/20
13		12/13/20											12/18/20
14		12/14/20											12/17/20
15		12/15/20											12/16/20
16		12/16/20											12/15/20
17		12/17/20											12/14/20
18		12/18/20											12/13/20
19		12/19/20											12/12/20
20		12/20/20											12/11/20
21		12/21/20											12/10/20
22		12/22/20											12/9/20
23													12/8/20
24
25
Clips

AND

Book2
	A	B
1	22
2
Sheet1

Cell Formulas
Range	Formula
A1	A1	=COUNTIFS(Clips!M1:M558,">"&"1/12/2020",Clips!B1:B558,"<"&"30/12/2020")

 

[wmichael]

Hi Etaf, still not working form me. In column M are listed numbers that I would like to sum (or count) only if they fall between two dates in the B column. I have copied your formula but not able to make it work.

 

[wmichael]

i changed my dates to UK
=COUNTIFS(Clips!M1:M558,">"&"1/12/2020",Clips!B1:B558,"<"&"30/12/2020")
works

What are UK dates? I formatted my date column to m/d/yyyy Are UK dates different than this? Should I change the format the column from dates to general?

 

[wmichael]

What are UK dates? I formatted my date column to m/d/yyyy Are UK dates different than this? Should I change the format the column from dates to general?

Okay found the reference and changed from English to United Kingdom and still not working.

 

[etaf]

Sorry , I misunderstood
A SUM of the values in column M - will be different to counting how many ROWS

Count dates in B that match
=COUNTIFS(Clips!B1:B558,">"&"1/12/2020",Clips!B1:B558,"<"&"30/12/2020")

SUM VALUES IN M

=SUMIFS(Clips!M1:M558,Clips!B1:B558,">"&"1/12/2020",Clips!B1:B558,"<"&"30/12/2020")
BUT whats in column M?

Need to see an example

UK dates are usually DD/MM/YY
and NOT the US style of DD/MM/YY

and excel depending on settings will see " DD/MM/YY"
differently , at least thats what I have found when using in formula like that

Are you in UK ?
COUNTIFS(Clips!M2:Clips!M560,">"&"12/1/2020",Clips!B2:Clips!B560,"<"&"12/30/2020")
is using the MM/DD/YYYY format, BUT as its in "" it assumes the system setting and IF NOT that format , then I think it will error

IF UK
change the date values in the ""

 

[wmichael]

Here is the XL2BB Clip of Sheet A

2019-22 Media Clips v03.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	SEA TOW FOUNDATION MEDIA CLIPS	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21
2	SUMMARY
3	187 Headlines
4	Distributed by 177 Outlets
5	Across 140 Cities
6	and 34 States
7	Produced a combined total of 106,365,110 Circulation, Impressions, and Event Attendees
8	With a total of $6,156,197 in Media Value
9	Consisting of 77 Print 278 and Web Headlines, and attendees to 0 Events that have seen our boating safety messages and break down to
10	2,610,214 Print Circulation
11	$142,030 Print Value
12	103,754,896 Web Impressions
13	$6,014,167 Web Value
14	0 Event Attendees
15
16	DETAILS / TOPICS
17	1 General Mention of Sea Tow Foundation
18	0 Mentions Covid19
19	0 Mentions Sea Ray Boats
20	0 Mentions MarineMax
21	0 Mentions Bonnier Corp
22	0 Mentions Yamaha Outboard
23	0 Mentions Miami Boat Show
24	0 Mentions National Safe Boating Week
25	0 Mentions Summer of Boating Safely																		1
26	0 Mentions Beacons
27	8 Mentions Life Jacket Loaner Program																SUMIFS(mc!m2:mc!H26,mc!bc2:mc!bc561,">=11/1/2020",mc!bc2:mc!bc561,"<=11/30/2020")	0	0
28	55,927 Life Jacket Impressions
29	Life Jacket Valued at $1,462
30	0 Mentions Life Jacket Loaner Data Base
31	0 Mentions Sober Skipper Program
32	0 Sober Skipper Impressions
33	Sober Skipper Mentions Valued at $0
34	0 Mentions Sober Skipper Awards
35	0 Mentions Flare Disposal Program
36	0 Flare Disposal Impressions
37	Flare Disposal Valued at $0
38	0 Mentions Social Distancing
39	0 CPR Generated
40	0 WKS Generated
41	0 Friends where media was distributed
42	4 Mentions Sea Tow Locations
43	1 Mentions Sea Care
44	3 Mentions US Coast Guard
45	1 Mentions State of New Hampshire
46	0 Mentions Kicker Audio
47	0 Mentions MRAA Dealer Week
48
49	TOP DISTRIBUTING OUTLETS
50	name
51	name
52	name
53	name
54	name
55	name
56	name
57	name
58	name
59	name
Dashboard

Cell Formulas
Range	Formula
D3	D3	=MC!AU1
D4	D4	=MC!AV1
D5	D5	=MC!AY1
D6	D6	=MC!AZ1
D7	D7	=MC!BB1
D8	D8	=MC!BC1
D9	D9	=MC!BD1
D10	D10	=MC!BH1
D11	D11	=MC!BI1
D12	D12	=MC!BJ1
D13	D13	=MC!BK1
D14	D14	=MC!BL1
V25	V25	=MC!M2
U27	U27	=COUNTIFS(MC!M2:MC!M560,">"&"11/1/2020",MC!BF2:MC!BF560,","&"11"/30/2020)
V27	V27	=COUNTIFS(MC!M2:MC!M561,">="&"12/1/2020",MC!BF2:MC!BF561,"<="&"12/30/2020")
D17	D17	=MC!A1
D18	D18	=MC!C1
D19	D19	=MC!D1
D20	D20	=MC!E1
D21	D21	=MC!F1
D22	D22	=MC!G1
D23	D23	=MC!H1
D24	D24	=MC!J1
D25	D25	=MC!K1
D26	D26	=MC!L1
D27	D27	=MC!M1
D28	D28	=MC!N1
D29	D29	=MC!O1
D30	D30	=MC!Q1
D31	D31	=MC!S1
D32	D32	=MC!T1
D33	D33	=MC!U1
D34	D34	=MC!V1
D35	D35	=MC!W1
D36	D36	=MC!X1
D37	D37	=MC!Y1
D38	D38	=MC!Z1
D39	D39	=MC!AA1
D40	D40	=MC!AB1
D41	D41	=MC!AC1
D42	D42	=MC!AE1
D43	D43	=MC!AF1
D44	D44	=MC!AG1
D45	D45	=MC!AH1
D46	D46	=MC!AI1
D47	D47	=MC!AJ1
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
MC!_FilterDatabase	=MC!$A$1:$BK$1	D17

 

[wmichael]

Here is the XL2BB Clip of Sheet A

2019-22 Media Clips v03.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	SEA TOW FOUNDATION MEDIA CLIPS	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21
2	SUMMARY
3	187 Headlines
4	Distributed by 177 Outlets
5	Across 140 Cities
6	and 34 States
7	Produced a combined total of 106,365,110 Circulation, Impressions, and Event Attendees
8	With a total of $6,156,197 in Media Value
9	Consisting of 77 Print 278 and Web Headlines, and attendees to 0 Events that have seen our boating safety messages and break down to
10	2,610,214 Print Circulation
11	$142,030 Print Value
12	103,754,896 Web Impressions
13	$6,014,167 Web Value
14	0 Event Attendees
15
16	DETAILS / TOPICS
17	1 General Mention of Sea Tow Foundation
18	0 Mentions Covid19
19	0 Mentions Sea Ray Boats
20	0 Mentions MarineMax
21	0 Mentions Bonnier Corp
22	0 Mentions Yamaha Outboard
23	0 Mentions Miami Boat Show
24	0 Mentions National Safe Boating Week
25	0 Mentions Summer of Boating Safely																		1
26	0 Mentions Beacons
27	8 Mentions Life Jacket Loaner Program																SUMIFS(mc!m2:mc!H26,mc!bc2:mc!bc561,">=11/1/2020",mc!bc2:mc!bc561,"<=11/30/2020")	0	0
28	55,927 Life Jacket Impressions
29	Life Jacket Valued at $1,462
30	0 Mentions Life Jacket Loaner Data Base
31	0 Mentions Sober Skipper Program
32	0 Sober Skipper Impressions
33	Sober Skipper Mentions Valued at $0
34	0 Mentions Sober Skipper Awards
35	0 Mentions Flare Disposal Program
36	0 Flare Disposal Impressions
37	Flare Disposal Valued at $0
38	0 Mentions Social Distancing
39	0 CPR Generated
40	0 WKS Generated
41	0 Friends where media was distributed
42	4 Mentions Sea Tow Locations
43	1 Mentions Sea Care
44	3 Mentions US Coast Guard
45	1 Mentions State of New Hampshire
46	0 Mentions Kicker Audio
47	0 Mentions MRAA Dealer Week
48
49	TOP DISTRIBUTING OUTLETS
50	name
51	name
52	name
53	name
54	name
55	name
56	name
57	name
58	name
59	name
Dashboard

Cell Formulas
Range	Formula
D3	D3	=MC!AU1
D4	D4	=MC!AV1
D5	D5	=MC!AY1
D6	D6	=MC!AZ1
D7	D7	=MC!BB1
D8	D8	=MC!BC1
D9	D9	=MC!BD1
D10	D10	=MC!BH1
D11	D11	=MC!BI1
D12	D12	=MC!BJ1
D13	D13	=MC!BK1
D14	D14	=MC!BL1
V25	V25	=MC!M2
U27	U27	=COUNTIFS(MC!M2:MC!M560,">"&"11/1/2020",MC!BF2:MC!BF560,","&"11"/30/2020)
V27	V27	=COUNTIFS(MC!M2:MC!M561,">="&"12/1/2020",MC!BF2:MC!BF561,"<="&"12/30/2020")
D17	D17	=MC!A1
D18	D18	=MC!C1
D19	D19	=MC!D1
D20	D20	=MC!E1
D21	D21	=MC!F1
D22	D22	=MC!G1
D23	D23	=MC!H1
D24	D24	=MC!J1
D25	D25	=MC!K1
D26	D26	=MC!L1
D27	D27	=MC!M1
D28	D28	=MC!N1
D29	D29	=MC!O1
D30	D30	=MC!Q1
D31	D31	=MC!S1
D32	D32	=MC!T1
D33	D33	=MC!U1
D34	D34	=MC!V1
D35	D35	=MC!W1
D36	D36	=MC!X1
D37	D37	=MC!Y1
D38	D38	=MC!Z1
D39	D39	=MC!AA1
D40	D40	=MC!AB1
D41	D41	=MC!AC1
D42	D42	=MC!AE1
D43	D43	=MC!AF1
D44	D44	=MC!AG1
D45	D45	=MC!AH1
D46	D46	=MC!AI1
D47	D47	=MC!AJ1
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
MC!_FilterDatabase	=MC!$A$1:$BK$1	D17

Here is the XL2BB Clip of Sheet B

2019-22 Media Clips v03.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF
1	1 General Mention of Sea Tow Foundation	?	0 Mentions Covid19	0 Mentions Sea Ray Boats	0 Mentions MarineMax	0 Mentions Bonnier Corp	0 Mentions Yamaha Outboard	0 Mentions Miami Boat Show	0 Mentions Freedom Boat Club	0 Mentions National Safe Boating Week	0 Mentions Summer of Boating Safely	0 Mentions Beacons	8 Mentions Life Jacket Loaner Program	55,927 Life Jacket Impressions	Life Jacket Valued at $1,462	4 Mentions Life Jacket Loaner Grant	0 Mentions Life Jacket Loaner Data Base	1 Mentions Hero Award	0 Mentions Sober Skipper Program	0 Sober Skipper Impressions	Sober Skipper Mentions Valued at $0	0 Mentions Sober Skipper Awards	0 Mentions Flare Disposal Program	0 Flare Disposal Impressions	Flare Disposal Valued at $0	0 Mentions Social Distancing	0 CPR Generated	0 WKS Generated	0 Friends where media was distributed	Saved a copy	4 Mentions Sea Tow Locations	1 Mentions Sea Care	3 Mentions US Coast Guard	1 Mentions State of New Hampshire	0 Mentions Kicker Audio	0 Mentions MRAA Dealer Week	#REF!	#REF!	0 articles and0 Freedom Boat Club Locations where media was distributed	0 Impressions	Valued at $0	#REF!	#REF!	0 Not STF	10 Article Saved	Burrelles Luce #	187 Headlines	Distributed by 177 Outlets	Across 1 Direct Media Areas	Dma Rank	Across 140 Cities	and 34 States	and 1 Countries	Produced a combined total of 106,365,110 Circulation, Impressions, and Event Attendees	With a total of $6,156,197 in Media Value	Consisting of 77 Print 278 and Web Headlines, and attendees to 0 Events that have seen our boating safety messages and break down to	Published Date	Posted Date
2													1	5252	132																Sea Tow Portland/Midcoast														1		Sea Tow Portland/Midcoast co-owners honored as 'Champions'	Boothbay Register (The)	N/A	N/A	BOOTHBAY HARBOR	ME	USA	5,252	$132	- Web	12-03-2020	07/12/2020
3													1	340	362																Sea Tow Southcentral Alaska														1		Capt. Trey and Alyssa Hill win Sea Tow best franchise award	Homer News	N/A	N/A	HOMER	AK	USA	340	$362	- Web	12-03-2020	03/12/2020
4	1													0	0																STSI	1													1		Sea Care by Sea Tow® Offers Service After the Sale	SOUNDINGS (TRADE ONLY)	N/A	N/A	ESSEX	CT	USA	27,272	$2,718	- Print	12-01-2020	03/12/2020
5													1	25400	217																		1	1											1		Grants Available for Life Jacket Loaner Stations	CARRIAGE TOWNE NEWS	N/A	9	KINGSTON	NH	USA	25,400	$217	- Print	11-26-2020	01/12/2020
6														0	0			1													Sea Tow Venice														1		Sea Tow International Presents Annual Awards - Trade Only Today	Soundings: Trade Only	N/A	N/A	ESSEX	CT	USA	3,034	$11	- Web	11-23-2020	24/11/2020
7													1	8500	374	1																													1		Sea Tow Accepting Applications For Life Jacket Loaner Program	Marina Dock Age	N/A	N/A	NILES	IL	USA	8,500	$374	- Web	11-13-2020	16/11/2020
8													1	3372	27	1																													1		Sea Tow Foundation Accepting Applications for Life Jacket Loaner Stations	Fishing Wire (The)	N/A	N/A	GLEN ALLEN	VA	USA	3,372	$27	- Web	11-13-2020	16/11/2020
9													1	3965	37	1																	1												1		Sea Tow Foundation accepting Life Jacket Loaner Station applications - Boating Industry	Boating Industry Online	N/A	N/A	MAPLE GROVE	MN	USA	3,965	$37	- Web	11-12-2020	16/11/2020
10													1	4698	28	1																	1												1		Life Jacket Loaner Station Program is Expanding - Trade Only Today	Soundings: Trade Only	N/A	N/A	ESSEX	CT	USA	4,698	$28	- Web	11-12-2020	16/11/2020
11														0	0																															1	PEOPLE & BUSINESS	NORTHERN FORECASTER	N/A	79	FALMOUTH	ME	USA	20,000	$651	- Print	11-05-2020	13/11/2020
12													1	4400	285																														1		Rick and Nancy Ellet win Karl Martin Award	SMITH MOUNTAIN EAGLE	N/A	69	MONETA	VA	USA	4,400	$285	- Print	10-07-2020	26/10/2020
13														0	0																																Suggestions for boaters age 50 and up	NEWS-PRESS	N/A	53	FORT MYERS	FL	USA	33,417	$1,140	- Print	10-22-2020	23/10/2020
14																																															Coast Guard rescues 6 near Sunshine Skyway	TAMPA BAY TIMES	N/A	12	TAMPA	FL	USA	240,024	$2,210	- Print	10-19-2020	19/10/2020
15														0	0																																SMLA recaps the year in new online format	ROANOKE TIMES	N/A	69	ROANOKE	VA	USA	29,484	$1,233	- Print	10-07-2020	09/10/2020
16														0	0																																Sea Tow Foundation announces deadline for National Boating Industry Safety Awards - Boating Industry	Boating Industry Online	N/A	N/A	MAPLE GROVE	MN	USA	3,965	$37	- Web	10-06-2020	07/10/2020
17																																															Sea Tow Foundation Announces Oct. 15 Deadline for National Boating Industry Safety Awards	Fishing Wire (The)	N/A	N/A	GLEN ALLEN	VA	USA	3,372	$29	- Web	10-06-2020	07/10/2020
18																																															Sea Tow Foundation Announces Deadline for National Boating Industry Safety Awards	Outdoor Wire (The)	N/A	N/A	GLEN ALLEN	VA	USA	60	$563	- Web	10-06-2020	07/10/2020
19																																															SCUTTLE	LAKELAND BOATING	N/A	N/A	EVANSTON	IL	USA	42,096	$3,762	- Print	10-01-2020	06/10/2020
20																																															Sea Tow Naples owner reflects on decade of business	Florida Weekly	N/A	N/A	FORT MYERS	FL	USA	8,097	$53	- Web	10-02-2020	05/10/2020
21																																															BUSINESS BUZZ	SARASOTA HERALD-TRIBUNE	N/A	12	SARASOTA	FL	USA	32,003	$1,637	- Print	10-01-2020	05/10/2020
22																																															Sea Tow Foundation building database for life jacket stations	THE LOG (SOUTHERN CALIFORNIA EDITION)	N/A	N/A	SAN DIEGO	CA	USA	40,000	$301	- Print	09-03-2020	28/09/2020
23																																															Social Distancing Leads to Spikes in Boating Activity	My Mother Lode	N/A	N/A	SONORA	CA	USA	17,485	$536	- Web	09-24-2020	28/09/2020
24																																															Social Distancing Leads to Spikes in Boating Activity	WRAL-TV Channel 5	N/A	N/A	RALEIGH	NC	USA	1,047,024	$30,437	- Web	09-24-2020	28/09/2020
25																																															Social Distancing Leads to Spikes in Boating Activity	Antlers American (The)	N/A	N/A	ANTLERS	OK	USA	1,000	$82	- Web	09-24-2020	28/09/2020
26																																															Social Distancing Leads to Spikes in Boating Activity	Minyanville	N/A	N/A	NEW YORK	NY	USA	2,622	$23	- Web	09-24-2020	28/09/2020
27																																															Social Distancing Leads to Spikes in Boating Activity	Wapakoneta Daily News	N/A	N/A	WAPAKONETA	OH	USA	2,009	$12	- Web	09-24-2020	28/09/2020
28																																															Social Distancing Leads to Spikes in Boating Activity	Townhall.com	N/A	N/A	WASHINGTON	DC	USA	575,843	$8,685	- Web	09-24-2020	28/09/2020
29																																															Social Distancing Leads to Spikes in Boating Activity	International Business Times	N/A	N/A	NEW YORK	NY	USA	1,118,055	$15,201	- Web	09-24-2020	28/09/2020
30																																															Social Distancing Leads to Spikes in Boating Activity	StreetInsider.com	N/A	N/A	BIRMINGHAM	MI	USA	95,198	$1,387	- Web	09-24-2020	28/09/2020
31																																															Social Distancing Leads to Spikes in Boating Activity	Borger News-Herald	N/A	N/A	BORGER	TX	USA	255	$460	- Web	09-24-2020	28/09/2020
32																																															Social Distancing Leads to Spikes in Boating Activity	San Diego Union Tribune	N/A	N/A	SAN DIEGO	CA	USA	756,386	$6,796	- Web	09-24-2020	28/09/2020
33																																															Social Distancing Leads to Spikes in Boating Activity	Morning News	N/A	N/A	BLACKFOOT	ID	USA	2,315	$12	- Web	09-24-2020	28/09/2020
34																																															Social Distancing Leads to Spikes in Boating Activity	Inyo Register	N/A	N/A	BISHOP	CA	USA	2,677	$12	- Web	09-24-2020	28/09/2020
35																																															Social Distancing Leads to Spikes in Boating Activity	Marketplace	N/A	N/A	LOS ANGELES	CA	USA	512,399	$3,998	- Web	09-24-2020	28/09/2020
MC

Cell Formulas
Range	Formula
A1	A1	=CONCATENATE(SUM(A2:A182), " General Mention of Sea Tow Foundation")
C1	C1	=CONCATENATE(SUM(C2:C182), " Mentions Covid19 ")
D1	D1	=CONCATENATE(SUM(D2:D182), " Mentions Sea Ray Boats ")
E1	E1	=CONCATENATE(SUM(E2:E182), " Mentions MarineMax ")
F1	F1	=CONCATENATE(SUM(F2:F182), " Mentions Bonnier Corp ")
G1	G1	=CONCATENATE(SUM(G2:G182), " Mentions Yamaha Outboard ")
H1	H1	=CONCATENATE(SUM(H2:H182), " Mentions Miami Boat Show")
I1	I1	=CONCATENATE(SUM(I2:I182), " Mentions Freedom Boat Club")
J1	J1	=CONCATENATE(SUM(J2:J182), " Mentions National Safe Boating Week ")
K1	K1	=CONCATENATE(SUM(K2:K182), " Mentions Summer of Boating Safely ")
L1	L1	=CONCATENATE(SUM(L2:L182), " Mentions Beacons ")
M1	M1	=CONCATENATE(SUM(M2:M182), " Mentions Life Jacket Loaner Program ")
N1	N1	=CONCATENATE(TEXT(SUM(N2:N146),"#,##0")," Life Jacket Impressions")
O1	O1	=CONCATENATE("Life Jacket Valued at $",TEXT(SUM(O2:O205),"#,##0"))
P1	P1	=CONCATENATE(SUM(P2:P182), " Mentions Life Jacket Loaner Grant ")
Q1	Q1	=CONCATENATE(SUM(Q2:Q182), " Mentions Life Jacket Loaner Data Base ")
R1	R1	=CONCATENATE(SUM(R2:R182), " Mentions Hero Award ")
S1	S1	=CONCATENATE(SUM(S2:S182), " Mentions Sober Skipper Program")
T1	T1	=CONCATENATE(TEXT(SUM(T2:T146),"#,##0")," Sober Skipper Impressions")
U1	U1	=CONCATENATE("Sober Skipper Mentions Valued at $",TEXT(SUM(U2:U205),"#,##0"))
V1	V1	=CONCATENATE(SUM(V2:V182), " Mentions Sober Skipper Awards")
W1	W1	=CONCATENATE(SUM(W2:W182), " Mentions Flare Disposal Program")
X1	X1	=CONCATENATE(TEXT(SUM(X2:X146),"#,##0")," Flare Disposal Impressions")
Y1	Y1	=CONCATENATE("Flare Disposal Valued at $",TEXT(SUM(Y2:Y205),"#,##0"))
Z1	Z1	=CONCATENATE(SUM(Z2:Z182), " Mentions Social Distancing")
AA1	AA1	=CONCATENATE(SUM(AA2:AA182), " CPR Generated")
AB1	AB1	=CONCATENATE(SUM(AB2:AB182), " WKS Generated")
AC1	AC1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(AC2:AC146)>0,MATCH(AC2:AC146,AC2:AC146,0),""),IF(LEN(AC2:AC146)>0,MATCH(AC2:AC146,AC2:AC146,0),""))>0,1))," Friends where media was distributed")
AE1	AE1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(AE2:AE146)>0,MATCH(AE2:AE146,AE2:AE146,0),""),IF(LEN(AE2:AE146)>0,MATCH(AE2:AE146,AE2:AE146,0),""))>0,1))," Mentions Sea Tow Locations")
AF1	AF1	=CONCATENATE(SUM(AF2:AF182)," Mentions Sea Care")
AG1	AG1	=CONCATENATE(SUM(AG2:AG182)," Mentions US Coast Guard")
AH1	AH1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(AH2:AH146)>0,MATCH(AH2:AH146,AH2:AH146,0),""),IF(LEN(AH2:AH146)>0,MATCH(AH2:AH146,AH2:AH146,0),""))>0,1))," Mentions State of New Hampshire")
AI1	AI1	=CONCATENATE(SUM(AI2:AI182)," Mentions Kicker Audio")
AJ1	AJ1	=CONCATENATE(SUM(AJ2:AJ182)," Mentions MRAA Dealer Week")
AK1	AK1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(#REF!)>0,MATCH(#REF!,#REF!,0),""),IF(LEN(#REF!)>0,MATCH(#REF!,#REF!,0),""))>0,1))," America's Boating Clubs located where Media was Distributed")
AL1	AL1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(#REF!)>0,MATCH(#REF!,#REF!,0),""),IF(LEN(#REF!)>0,MATCH(#REF!,#REF!,0),""))>0,1))," Boat Shows and Events located where Media was Distributed")
AM1	AM1	=CONCATENATE(SUM(AM2:AM205)," articles and",SUM(IF(FREQUENCY(IF(LEN(AM2:AM146)>0,MATCH(AM2:AM146,AM2:AM146,0),""),IF(LEN(AM2:AM146)>0,MATCH(AM2:AM146,AM2:AM146,0),""))>0,1))," Freedom Boat Club Locations where media was distributed")
AN1	AN1	=CONCATENATE(TEXT(SUM(AN2:AN146),"#,##0")," Impressions")
AO1	AO1	=CONCATENATE("Valued at $",TEXT(SUM(AO2:AO205),"#,##0"))
AP1	AP1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(#REF!)>0,MATCH(#REF!,#REF!,0),""),IF(LEN(#REF!)>0,MATCH(#REF!,#REF!,0),""))>0,1))," Scout Groups located where Media was Distributed")
AQ1	AQ1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(#REF!)>0,MATCH(#REF!,#REF!,0),""),IF(LEN(#REF!)>0,MATCH(#REF!,#REF!,0),""))>0,1))," US Coast Guard Flotilla Aux located where Media was Distributed")
AR1	AR1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(AR2:AR146)>0,MATCH(AR2:AR146,AR2:AR146,0),""),IF(LEN(AR2:AR146)>0,MATCH(AR2:AR146,AR2:AR146,0),""))>0,1))," Not STF")
AS1	AS1	=CONCATENATE(SUM(AS2:AS422)," Article Saved")
AU1	AU1	=CONCATENATE(SUM(IF(FREQUENCY(IF(LEN(AU2:AU505)>0,MATCH(AU2:AU505,AU2:AU505,0),""),IF(LEN(AU2:AU505)>0,MATCH(AU2:AU505,AU2:AU505,0),""))>0,1))," Headlines")
AV1	AV1	=CONCATENATE("Distributed by ",SUM(IF(FREQUENCY(IF(LEN(AV2:AV505)>0,MATCH(AV2:AV505,AV2:AV505,0),""),IF(LEN(AV2:AV505)>0,MATCH(AV2:AV505,AV2:AV505,0),""))>0,1))," Outlets")
AW1	AW1	=CONCATENATE("Across ",SUM(IF(FREQUENCY(IF(LEN(AW2:AW505)>0,MATCH(AW2:AW505,AW2:AW505,0),""),IF(LEN(AW2:AW505)>0,MATCH(AW2:AW505,AW2:AW505,0),""))>0,1))," Direct Media Areas")
AY1	AY1	=CONCATENATE("Across ",SUM(IF(FREQUENCY(IF(LEN(AY2:AY505)>0,MATCH(AY2:AY505,AY2:AY505,0),""),IF(LEN(AY2:AY505)>0,MATCH(AY2:AY505,AY2:AY505,0),""))>0,1))," Cities")
AZ1	AZ1	=CONCATENATE("and ",SUM(IF(FREQUENCY(IF(LEN(AZ2:AZ505)>0,MATCH(AZ2:AZ505,AZ2:AZ505,0),""),IF(LEN(AZ2:AZ505)>0,MATCH(AZ2:AZ505,AZ2:AZ505,0),""))>0,1))," States")
BA1	BA1	=CONCATENATE("and ",SUM(IF(FREQUENCY(IF(LEN(BA2:BA505)>0,MATCH(BA2:BA505,BA2:BA505,0),""),IF(LEN(BA2:BA505)>0,MATCH(BA2:BA505,BA2:BA505,0),""))>0,1))," Countries")
BB1	BB1	=CONCATENATE("Produced a combined total of ",TEXT(SUM(BB2:BB505),"#,##0")," Circulation, Impressions, and Event Attendees")
BC1	BC1	=CONCATENATE("With a total of $",TEXT(SUM(BC2:BC505),"#,##0")," in Media Value")
BD1	BD1	=CONCATENATE("Consisting of ",COUNTIF(BD2:BD505,"*Print*")," Print ",COUNTIF(BD2:BD505,"*Web*")," and Web Headlines, and attendees to ",COUNTIF(BD2:BD505,"*Event*")," Events"," that have seen our boating safety messages and break down to")
N2:N13	N2	=IF(M2=1,BB2,"0")
O2:O13	O2	=IF(M2=1,BC2,"0")
N15:N16	N15	=IF(M15=1,#REF!,"0")
O15:O16	O15	=IF(M15=1,#REF!,"0")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AT:AT	Cell Value	duplicates	text	NO

 

[wmichael]

Sorry , I misunderstood
A SUM of the values in column M - will be different to counting how many ROWS

Count dates in B that match
=COUNTIFS(Clips!B1:B558,">"&"1/12/2020",Clips!B1:B558,"<"&"30/12/2020")

SUM VALUES IN M

=SUMIFS(Clips!M1:M558,Clips!B1:B558,">"&"1/12/2020",Clips!B1:B558,"<"&"30/12/2020")
BUT whats in column M?

Need to see an example

UK dates are usually DD/MM/YY
and NOT the US style of DD/MM/YY

and excel depending on settings will see " DD/MM/YY"
differently , at least thats what I have found when using in formula like that

Are you in UK ?
COUNTIFS(Clips!M2:Clips!M560,">"&"12/1/2020",Clips!B2:Clips!B560,"<"&"12/30/2020")
is using the MM/DD/YYYY format, BUT as its in "" it assumes the system setting and IF NOT that format , then I think it will error

IF UK
change the date values in the ""

I changed the dates back to English United States and Countifs to Sumifs and still not working.