Formula help for countifs with multiple criteria
Hello,
I would really appreciate some help crafting a formula for a dashboard. Thank you in advance for your help!
Goal: count based on current year
Criteria: field type (lead time) within the current year (request date) referencing a different worksheet
Formula attempt:
=COUNTIFS({Lead Time}, HAS(@cell, Type@row), AND({Request Date}, >=DATE(2020, 1, 1), {Request Date}, <=DATE(2020, 12, 31)))
Result: #Invalid Data Type
Would like to also leverage for the summation of costs based on current year.
Best Answer
-
Only a few tweaks were needed. If we use the YEAR function, we can designate the year which will eliminate the need for inclusive date range. Date functions (Year, Month, etc) can cause errors so the IFERROR helps with that.
Also, without seeing your dataset, it wasn't clear if you needed the HAS function or not. Are you working with multi-select dropdowns? If not, we can just use Type@row to find the data.
I'll give you formulas with and without the HAS function
=COUNTIFS({Lead Time}, HAS(@cell, Type@row), {Request Date}, IFERROR(YEAR(@cell),0)=2020)
No HAS()
=COUNTIFS({Lead Time}, Type@row, {Request Date}, IFERROR(YEAR(@cell),0)=2020)
Does this work for you? If yes, to use for Costs, you would use SUMIFS. If the criteria is still the same, you would add whatever range you wanted Summed (Cost?) in addition to all the terms used in your COUNTIFS. =SUMIFS({your cost range}, {Lead Time}, Type@row, {Request Date}, IFERROR(YEAR(@cell),0)=2020)
PS. If you did need the inclusive date range the formula would look like this. If you're using AND function, it is part of the criteria portion, not the range portion of the formula.
=COUNTIFS({Lead Time}, Type@row, {Request Date}, AND(>=DATE(2020, 1, 1), <=DATE(2020, 12, 31)))
cheers
Answers
-
Only a few tweaks were needed. If we use the YEAR function, we can designate the year which will eliminate the need for inclusive date range. Date functions (Year, Month, etc) can cause errors so the IFERROR helps with that.
Also, without seeing your dataset, it wasn't clear if you needed the HAS function or not. Are you working with multi-select dropdowns? If not, we can just use Type@row to find the data.
I'll give you formulas with and without the HAS function
=COUNTIFS({Lead Time}, HAS(@cell, Type@row), {Request Date}, IFERROR(YEAR(@cell),0)=2020)
No HAS()
=COUNTIFS({Lead Time}, Type@row, {Request Date}, IFERROR(YEAR(@cell),0)=2020)
Does this work for you? If yes, to use for Costs, you would use SUMIFS. If the criteria is still the same, you would add whatever range you wanted Summed (Cost?) in addition to all the terms used in your COUNTIFS. =SUMIFS({your cost range}, {Lead Time}, Type@row, {Request Date}, IFERROR(YEAR(@cell),0)=2020)
PS. If you did need the inclusive date range the formula would look like this. If you're using AND function, it is part of the criteria portion, not the range portion of the formula.
=COUNTIFS({Lead Time}, Type@row, {Request Date}, AND(>=DATE(2020, 1, 1), <=DATE(2020, 12, 31)))
cheers
-
Thank you so much @KDM! Worked perfect!
The HAS function was suggested to me by Smartsheet for multi-select lists when I need to pull counts.
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!