COUNTIF + LAST YEAR INFO
=COUNTIFS({M&A Program Interest}, CONTAINS("Buyer Consult", @cell), {M&A Intake Form Range 1}, @cell > YEAR
Trying to utilize this formula; pull metrics from the past year only.
Answers
-
I responded to your other post.
Here's the formula I provided:
=IF(AND(Year@row = 2022, [Range 1]@row = "Buyer Consult"), "Match", "No Match")
Projects Delivered. Data Defended.
-
Not quite.
=COUNTIFS({M&A Program Interest}, CONTAINS("Buyer Consult", @cell), {M&A Intake Form Range 1}, @cell > TODAY(-268))
This is my current formula. Is there an easier way than changing the subtract amount everyday. I am sure there is
-
Hi @tmagana,
You can skip having a year column if you're just looking at previous year only:
=COUNTIFS({Date range}, IFERROR(YEAR(@cell), 0) = (YEAR(TODAY()) - 1), {M&A Program Interest}, CONTAINS("Buyer Consult", @cell))
The cross sheet references would be pointing that the relevant columns:
If you wanted by fixed year rather than rolling, you would change the (YEAR(TODAY() -1) to the relevant year (e.g. 2022). Equally you can increase the number in this to look further back (e.g. compare with 2 years ago).
If your M&A Program Interest column is a single select drop down rather than needing to look for the phrase you can also drop the CONTAINS("Buyer Consult",@cell) and just use "Buyer Consult" instead.
Hope this helps somewhat, but if you've problems/questions then just post!
-
=COUNTIF({M&A Intake Form}, IFERROR(YEAR(@cell), 0 = (YEAR(TODAY())-1),{M&A Program Interest}, CONTAINS("Buyer Consult", @cell)))))
Am I inputting it wrong?
Here are my columns
-
Hey @tmagana
Just jumping in here because it looks like all you're missing is a closing parentheses after the first YEAR function!
=COUNTIF({M&A Intake Form}, IFERROR(YEAR(@cell), 0) = (YEAR(TODAY())-1),{M&A Program Interest}, CONTAINS("Buyer Consult", @cell))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!