Tying a COUNTIFS formula on two sheets

Let me start by saying this is a bit complicated, and formulas are not my strength.

With that, here we go, I built a template for tracking warranty tickets which consists of several sheets, reports, and dashboards.

I'm trying to write a formula to calculate certain action dates (see table below)

This formula for the in-progress tickets works fine; =COUNTIF({CORE Warranty Trax Range 1}, =2022) however, to capture a true number of tickets, I need to include the completed tickets.

These tickets are moved to a separate sheet (Completed Warranty TIckets) by way of an automated workflow.

When I include the completed sheet in the formula the result is #INVALID OPERATION

=COUNTIFS({CORE Warranty Trax Range 1}, ({Resolved Date Range}), =2022)

Hope that makes sense. LMK if you need me to share the sheets I can do that.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer βœ“

    Hi @KD Harrigan

    You're close! πŸ™‚

    A COUNTIF function cannot look into two separate sheets. You'll need to write separate formulas for each sheet, then add them together, like so:

    =COUNTIFS(---) + COUNTIFS(---)

    Or in your case, something like:

    =COUNTIFS({CORE Warranty Trax Range 1}, =2022) + COUNTIFS({Resolved Date Range}, =2022)


    Can I also clarify, are you looking at a column that simply lists the Year (2022), or are you looking at a Date Range, as in a Date type of column? If you're looking at a Date column, you can use other functions to extract the year from the date. Let me know if the formula above worked for you or not, and I'd be happy to help further.

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer βœ“

    Hi @KD Harrigan

    You're close! πŸ™‚

    A COUNTIF function cannot look into two separate sheets. You'll need to write separate formulas for each sheet, then add them together, like so:

    =COUNTIFS(---) + COUNTIFS(---)

    Or in your case, something like:

    =COUNTIFS({CORE Warranty Trax Range 1}, =2022) + COUNTIFS({Resolved Date Range}, =2022)


    Can I also clarify, are you looking at a column that simply lists the Year (2022), or are you looking at a Date Range, as in a Date type of column? If you're looking at a Date column, you can use other functions to extract the year from the date. Let me know if the formula above worked for you or not, and I'd be happy to help further.

    Cheers,

    Genevieve

  • KD Harrigan
    KD Harrigan ✭✭✭

    Thank you, Genevieve, that works perfectly! Everyone is so helpful in this community.

    You're correct, I was trying to use COUNTIF on two separate sheets, lesson learned!

    I have hidden columns for the Year, Month, and Day in lieu of using the Date type columns. I find it somewhat easier to write formulas that way, and I'm always finding more data that I what to include on the dashboards as I continue to build this template out for our company.

    Have a fabulous weekend 😎

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @KD Harrigan

    I'm glad I could help! I also completely understand about breaking out your dates into multiple columns; it definitely makes a cross-sheet formula like this one much easier.

    I hope you have a fantastic weekend as well 😎

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!