Error with COUNTIFs and date formula
Hello!
Can someone please advise what am I doing wrong getting this error?
The formula used is as follows:
=COUNTIFS({ref sheet}, AND(MONTH(@cell) = 1, YEAR(@cell) = 2021))
The date column in the reference sheet is set as "Date" and the format is DD-Mon-YYYY (e.g. 19-Jan-2021). I've tried creating a default date column in my metrics sheet too, however, it made no change.
Any help is appreciated!
Best Answer
-
Hi @Michaela Kamenska,
Hope you are fine, please try the following formula
=COUNTIFS({ref sheet}, IFERROR(YEAR(@cell), 0) = 2021, {ref sheet}, IFERROR(MONTH(@cell), 0) = 1)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hi @Michaela Kamenska,
Hope you are fine, please try the following formula
=COUNTIFS({ref sheet}, IFERROR(YEAR(@cell), 0) = 2021, {ref sheet}, IFERROR(MONTH(@cell), 0) = 1)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi @Bassam.M Khalil ,
Thank you for your response. I appreciate your solution, however, this is a dynamic sheet where many rows are added every day. Is there a solution where I can use the whole column as a ref?
-
sorry i correct the formula for you please read my response again
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thank you very much, it works great!
-
Happy to help you any time @Michaela Kamenska
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!