# 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?

• ✭✭✭✭✭✭

@PStewart Try this:

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

• ✭✭✭✭✭

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

• ✭✭
edited 11/28/23

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)

• ✭✭✭✭✭✭

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

• ✭✭✭✭
edited 11/29/23

hello PStewart

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

• ✭✭✭✭✭✭

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:[email protected] | 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:[email protected] | 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"))

• ✭✭✭✭✭✭

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:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!