How do I add an additional piece of criteria to this formula
This is the current formula
=COUNTIF({CAR Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)))
I want it only to count the cells with this date range AND that have the word Toyota.
How would I modify the current formula?
Best Answer
-
@PStewart Try this:
=COUNTIFS({CAR Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)),{CAR Range 2}, HAS(@cell, "Toyota"))
Answers
-
Use a COUNTIFS and add that additional criteria.
=COUNTIFS(Count Range 1, Count Criteria, Count Range 2, Count 2 Criteria)
Criteria 1 would be the dates, Criteria two would be the Toyota being in the selected option in its column.
Let me know if this does not help!
If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.
Thanks!
Nick Stafford
-
I tried this and it comes back with an error:
=COUNTIFS({CAR Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)),{CAR Range 2}, Toyota)
Comments welcome
-
-
Right you are. I still get an error...
-
As Toyota is not a numerical value, you need to put this portion inside "":
=COUNTIFS({CAR Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)),{CAR Range 2}, "Toyota")
-
hello PStewart
what about this formula ?
=COUNTIFS({Car_range}, "Toyota", {date_range}, MONTH(@cell) = 1, {date_range}, YEAR(@cell) = 2022)
Best regards,
Florian
PS: as a basis I used the "Generate with AI" feature, by just prompting "count how many toyota date from 2022" and got this as an output : =COUNTIFS(Brand:Brand, "Toyota", Date:Date, YEAR(@cell) = 2022).
and after some minor adjustments, it works in my case ! impressive !
-
Hi @PStewart
I hope you're well and safe!
What error message are you getting?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you everyone, but none of the suggestions have worked unfortunately.
This is the error #unparseable
-
Happy to help!
Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I am trying count the number of times toyota shows up within a given month.
I am referencing another smartsheet for the word toyota and for the date field.
=COUNTIFS({CAR Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)),{CAR Range 2}, "Toyota")
Is not working, and I am wondering if it is because in the column where I look for toyota, it is a drop down which allows multiple selections, eg, ford, toyota, BMW
-
@PStewart Try this:
=COUNTIFS({CAR Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)),{CAR Range 2}, HAS(@cell, "Toyota"))
-
Does it allow more than one selection at a time? If so, you can use CONTAINS().
- =COUNTIFS({CAR Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)),{CAR Range 2}, CONTAINS("Toyota"))
Hope this helps!
Best,
Zach Hall
Training Delivery Manager / Charter Communications
-
Thank you all for your help!
HAS(@cell, "toyota"))
made this work perfectly!
-
Here's more info about the HAS function.
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62K Get Help
- 348 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 132 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!