Need help on countifs formula
Hi to everyone!
Appreciate to help me out to figure out why below formula is incorrect arguement?
=COUNTIFS({OPEN PR list Range 9}, [Primary Column]@row, {OPEN PR list Range 13}, <=DATE(2024, 4, 1), {OPEN PR list Range 13}, >=DATE(2024, 4, 30))
If i use no date range it works.
This is how the data file looks like
I wanted to count how many the purchaser has done based on the date range which monthly.
Thank you and awaiting anxiously.
Answers
-
After looking at your formula you have the < and > in the wrong locations. The way you have it you are looking for a date that's less then 2004,4,1 and also greater then 2004,4,30. There is no date that would exist in that range. Try.
=COUNTIFS({OPEN PR list Range 9}, [Primary Column]@row, {OPEN PR list Range 13}, >=DATE(2024, 4, 1), {OPEN PR list Range 13}, <=DATE(2024, 4, 30))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Did you try leaving only the date arguments to see if it works?
Are all the ranges the same size? They all use a full column?
I ran a test and it seems to work for me but I am probably missing something.
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
Hi @Itai,
Thank you for the comment but i did it as you can see below it works but when i add purchaser name it is telling me the incorrect arguement?
I am really confused why i could not have these 2 arguements to work together.
-
Can you double check that both of your {ranges} are in the same sheet and the same length?
This error message can appear if your ranges are different lengths. I would suggest edit each reference and click the column name so all rows are selected. Let us know if that works for you!
Cheers,
GenevieveJoin us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi @Genevieve P. ,
Yes both ranges are in one reference sheet.
-
It's good that they're on the same sheet, but can you confirm they're the same length?
For example, if {list purchaser 9} is referencing rows 1-5, but the {Date} reference is from rows 1-10, then you'll get an error. They have to have an equal number of rows in each reference.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!