Formula Help: Countif last 30 days and contains "phrase"

[indiemusicboy]

Hello. First time poster, long time lurker.

I'm working on a formula in worksheet A to pull data from worksheet B in the same workbook.

I have a formula that counts the entries if it meets a keyword criteria from a survey:
=COUNTIF('Data_Delivery Team'!J:P,"Very Satisfied")

... which works fine. But now...

I want to add an additional criteria that only gives me the counts of that formula above - if in the last 30 days:
=COUNTIFS('Data_Delivery Team'!B2:B6, "<"&TODAY()-7, 'Data_Delivery Team'!J:P,"Very Satisfied")

... but that formula is giving me a VALUE error.

Column B = Dates submitted
Columns J-P = Survey input

 

[indiemusicboy]

NOTE: regardless the formula saying 7 or 30 in the "<"&TODAY()-7 the result bounces back the VALUE error.

Just wanted to clarify that since I say 30 but the formula notes 7.

 

[Eric W]

You get the VALUE error because the 2 ranges are different sizes (B2:B6 versus J:P). What columns can say "Very Satisfied"? How many rows?

 

[indiemusicboy]

You get the VALUE error because the 2 ranges are different sizes (B2:B6 versus J:P). What columns can say "Very Satisfied"? How many rows?

If I change B2:B6 to B:B i still get the VALUE error.

 

[indiemusicboy]

If I change B2:B6 to B:B i still get the VALUE error.

Unless you mean one column (B) vs multiple columns (J thru P).

Columns J thru P are 7 questions (1 each column) with individual customer responses per row.

 

[indiemusicboy]

 

[Eric W]

When you use COUNTIFS, all the ranges must be the exact same size, both the number of rows and the number of columns. In your example, you have one column with dates, and 7 columns with responses. That won't work. If you use COUNTIFS, you can make it work by creating a COUNTIFS for every column in the J:P range, like the B8 formula below:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	3/20/2020
3	3/9/2020
4	3/9/2020									N/A	Very Satisfied	Very Satisfied	N/A	Very Satisfied	N/A	Somewhat satisfied
5	3/20/2020									Very Satisfied	Very Satisfied	Very Satisfied	Very Satisfied	Very Satisfied	Very Satisfied	Very Satisfied
6	4/1/2020									Very Satisfied	Very Satisfied	Very Satisfied	Very Satisfied	Very Satisfied	Very Satisfied	Very Satisfied
7
8		14	14
Sheet15

Cell Formulas
Range	Formula
B8	B8	=COUNTIFS(A2:A6,">="&TODAY()-30,J2:J6,"Very Satisfied")+COUNTIFS(A2:A6,">="&TODAY()-30,K2:K6,"Very Satisfied")+COUNTIFS(A2:A6,">="&TODAY()-30,L2:L6,"Very Satisfied")+COUNTIFS(A2:A6,">="&TODAY()-30,M2:M6,"Very Satisfied")+COUNTIFS(A2:A6,">="&TODAY()-30,N2:N6,"Very Satisfied")+COUNTIFS(A2:A6,">="&TODAY()-30,O2:O6,"Very Satisfied")+COUNTIFS(A2:A6,">="&TODAY()-30,P2:P6,"Very Satisfied")
C8	C8	=SUMPRODUCT((J2:P6="Very satisfied")*(A2:A6>=TODAY()-30))

Not especially user friendly. But the C8 formula works on a different principle, and should do what you want. Note these are tested on Windows, they should work on a Mac, but I can't test it. For the SUMPRODUCT formula, you should not use whole column references. Pick a row somewhere south of where you think you'll reach. Whole column references are OK in COUNTIFS since Excel is "aware" of the last row. So there's a tradeoff.

 

[indiemusicboy]

Awesome. This worked! Thank you!!

 

[Eric W]

Glad it works for you! Thanks for the feedback.