Using COUNTIFS to count number of occurrences within two different sheets = UNPARSEABLE
Hello - This is my first time using this community. I am trying count across two sheets to count the number of times a name is present in a column. They could be contained in both or just one sheet. My formula is continually wrong and from experience I know that it is likely something little that I am missing. Here is the latest one I have tried:
=COUNTIFS({Polaris minus CSC Creator}, =Creator@row, {Polaris minus CSC Date}, >=DATE(2022, 1, 1), {Polaris minus CSC Date}, <=DATE(2022, 12, 31)), {Polaris CSC Open}, OR({Polaris CSC Range 2}, =Creator@row, {Polaris CSC Open}, >=DATE(2022, 1, 1), {Polaris CSC Open}, <=DATE(2022, 12, 31))
Best Answer
-
I understand what you are attempting to do, but I am not 100% confident that I interpreted which references belong to which sheet in the formula. Try this and see if it works:
=COUNTIFS({Polaris minus CSC Creator}, =Creator@row, {Polaris minus CSC Date}, >=DATE(2022, 1, 1), {Polaris minus CSC Date}, <=DATE(2022, 12, 31)) + COUNTIFS(({Polaris CSC Range 2}, =Creator@row, {Polaris CSC Open}, >=DATE(2022, 1, 1), {Polaris CSC Open}, <=DATE(2022, 12, 31))
The general concept here is to break your formula up into two different COUNTIFS statements. The first half of your formula seems to be exactly right. You should then essentially write the same formula for the second sheet and add the results of those two statements together.
Answers
-
I understand what you are attempting to do, but I am not 100% confident that I interpreted which references belong to which sheet in the formula. Try this and see if it works:
=COUNTIFS({Polaris minus CSC Creator}, =Creator@row, {Polaris minus CSC Date}, >=DATE(2022, 1, 1), {Polaris minus CSC Date}, <=DATE(2022, 12, 31)) + COUNTIFS(({Polaris CSC Range 2}, =Creator@row, {Polaris CSC Open}, >=DATE(2022, 1, 1), {Polaris CSC Open}, <=DATE(2022, 12, 31))
The general concept here is to break your formula up into two different COUNTIFS statements. The first half of your formula seems to be exactly right. You should then essentially write the same formula for the second sheet and add the results of those two statements together.
-
Thank you Carson! This matches the formula I ended the evening with. I found the problem this morning as well, my reference sheet link somehow got mixed. THANK YOU for your help.
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!