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?
@PStewart Try this:
=COUNTIFS({CAR Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)),{CAR Range 2}, HAS(@cell, "Toyota"))
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!
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
Hi @PStewart,
I think your formula needs to start =COUNTIFS not =COUNTIF
John
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!
Thank you everyone, but none of the suggestions have worked unfortunately.
This is the error #unparseable
@PStewart
Happy to help!
Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.
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.
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
Does it allow more than one selection at a time? If so, you can use CONTAINS().
Hope this helps!
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!
Happy to help. 👍️
ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.
