How to combine 2 Countif queries where 1 is a date in the next 30 days

shecodesit ✭✭
edited 12/09/19 in Formulas and Functions

Let's say I have 2 columns: Widget Colors and Order Date.  I'm looking for a formula where I get the number of "red widgets" that have an order date in the next 30 days.

I have tried this formula without success: =COUNTIFS({2019 Widget Reporting Range 1}, "Project") AND ( {2019 Widget Reporting Range 2}, <=TODAY(30))



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It's just some basic syntax issues.

    1. When using a COUNTIFS function, the AND is implied meaning it will only count if all sets of range/criteria are true.

    2. You don't need a closing parenthesis in the middle of the function.


    Current Formula:

    =COUNTIFS({2019 Widget Reporting Range 1}, "Project") AND ( {2019 Widget Reporting Range 2}, <=TODAY(30))


    Tweaked formula:

    =COUNTIFS({2019 Widget Reporting Range 1}, "Project", {2019 Widget Reporting Range 2}, <=TODAY(30))

  • shecodesit

    It seems to be combining.  How would you create a union between the two columns "Widget Colors" and "Order Date".

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!