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

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
18
Office Version
  1. 365
Platform
  1. MacOS
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
18
Office Version
  1. 365
Platform
  1. MacOS
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
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

New Member
Joined
Apr 9, 2020
Messages
18
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Apr 9, 2020
Messages
18
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

New Member
Joined
Apr 9, 2020
Messages
18
Office Version
  1. 365
Platform
  1. MacOS
Screen Shot 2020-04-09 at 4.41.36 PM.png
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640

ADVERTISEMENT

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
ABCDEFGHIJKLMNOP
1
23/20/2020
33/9/2020
43/9/2020N/AVery SatisfiedVery SatisfiedN/AVery SatisfiedN/ASomewhat satisfied
53/20/2020Very SatisfiedVery SatisfiedVery SatisfiedVery SatisfiedVery SatisfiedVery SatisfiedVery Satisfied
64/1/2020Very SatisfiedVery SatisfiedVery SatisfiedVery SatisfiedVery SatisfiedVery SatisfiedVery Satisfied
7
81414
Sheet15
Cell Formulas
RangeFormula
B8B8=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")
C8C8=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,379
Messages
5,601,270
Members
414,439
Latest member
norideen

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