How do I add an additional piece of criteria to this formula

PStewart

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?

Paul Newcome

@PStewart Try this:

=COUNTIFS({CAR Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)),{CAR Range 2}, HAS(@cell, "Toyota"))

NickStaffordPM

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!

PStewart

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

John_Foster

Hi @PStewart,

I think your formula needs to start =COUNTIFS not =COUNTIF

John

PStewart

Right you are. I still get an error...

Nick Korna

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")

florian.zbinden7

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 !

Andrée Starå

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!

PStewart

Thank you everyone, but none of the suggestions have worked unfortunately.

This is the error #unparseable

Andrée Starå

@PStewart

Happy to help!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

PStewart

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

Paul Newcome

@PStewart Try this:

=COUNTIFS({CAR Range 1}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)),{CAR Range 2}, HAS(@cell, "Toyota"))

Zachary Hall

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!

PStewart

Thank you all for your help!

HAS(@cell, "toyota"))

made this work perfectly!

Andrée Starå

@PStewart

Here's more info about the HAS function.

https://help.smartsheet.com/function/has

✅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!

Paul Newcome

Happy to help. 👍️