Countif in last 3 months
Hi, I need to count if certain data is used in 6 different columns. Here is what I have so far:
=COUNTIF({Test Range 1}, [Name]@row) + COUNTIF({Test Range 2}, [Name]@row) + COUNTIF({Test Range 3}, [Name]@row) + COUNTIF({Test Range 4}, [Name]@row) + COUNTIF({Test Range 5}, [Name]@row) + COUNTIF({Test Range 6}, [Name]@row)
The above formula works but I need to somehow combine it with the below formula to calculate if any of the 'Test Range' data was inputted in the last 3 months:
=COUNTIFS({Test Date Range 1}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 3), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 3))
Apologies if this is really simple but I've been trying to get this to work for days.
Below is a screenshot of the sample data I have:
Thank you for any help you can provide,
Answers
-
Hey @Lewis Irving
Let's see if this will work for you
=COUNTIFS({Test Range 1}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) + COUNTIFS({Test Range 2}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) + COUNTIFS({Test Range 3}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) + COUNTIFS({Test Range 4}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) + COUNTIFS({Test Range 5}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY()))) + COUNTIFS({Test Range 6}, Name@row, {Test Date},AND(IFERROR(MONTH(@cell),0)=MONTH(TODAY())-3, IFERROR(YEAR(@cell),0)=YEAR(TODAY())))
Since you want to add dates, you must also add the range the dates come from
Will this work?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!