Tying a COUNTIFS formula on two sheets

KD Harrigan

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.

Genevieve P.

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

Genevieve P.

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

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.

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

Karene Hernandez

This was helpful.