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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!