COUNTIFS With Multiple References

Hugo Sanchezzz

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

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!