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?
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 381 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!