COUNTIFS With Multiple References


Hi all,

I'm clearly not understanding the @cell and other references quite right. I'm working to create a summary sheet referencing the same data sheet, but using multiple columns to compare. Example here:

Data sheet columns:

Assignee [user name]

Finish Date [date]

Summary columns:

Assignee [user name]

Ticket Count

Ticket Count 2020

Ticket Count 2021

The formula for ticket count overall isn't an issue; I've used this:

=COUNTIF({3DPro_-_Custom_Color Range 2}, FIND(Assignee@row, @cell) > 0)

When trying to get ticket count for a given year, I need to leverage the finish date column:

=COUNTIFS({3DPro_-_Custom_Color Range 2}, FIND(Assignee@row, @cell) > 0, {3DPro_-_Custom_Color Range 7}, YEAR(@cell) = 2020)

It's this YEAR(@cell) reference that is clearly bombing out, it seems. I need @cell to reference the {3DPro_-_Custom_Color Range 7} lookup, and I'm not certain it is doing that.

Any pointers?


  • Garrett Henke
    Garrett Henke ✭✭✭✭✭
    edited 06/09/21

    If I am understanding your formula right the formula should read

    COUNTIFS({3DPro_-_Custom_Color Range 2}, FIND(Assignee@row, @cell) > 0, {3DPro_-_Custom_Color Range 7}, YEAR([Finish Date]:[Finish Date]) = 2020)

    if that doesn't work upload a screenshot of the formula and columns so that i can get a better idea of what you are trying to do!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!