How can I compare two cells in a cross sheet reference?
Hello,
I have a COUNTIFS formula (below) that returns the number of items that pass all the tests.
Basically, count an item if it's unique id is not blank, it's % complete is 100, and the risk area is Antiboycott. This formula works beautifully.
=COUNTIFS({ExportUniqueID}, NOT(ISBLANK(@cell)), {ExpComp}, @cell = 1, {ExportWPRA}, ="Antiboycott")
I want to add one more test to determine if the projected end date and the actual end date are the same, indicating that the task complete on schedule. I'm having a problem with syntax. I tried the following, and got #UNPARSEABLE.
=COUNTIFS({ExportUniqueID}, NOT(ISBLANK(@cell)), {ExpComp}, @cell = 1, {ExportWPRA}, ="Antiboycott", {ExpProjEnd}@cell = {ExpActEnd}@cell)
I'm struggling with how to format the last test. Any help is truly appreciated!
Thanks
Janet
Best Answer
-
You would need to add in a helper column on the source sheet such as a checkbox. In that column you would use a basic IF formula to check the box if the dates match.
Then your cross sheet formula would look at this new helper column and include the criteria for if the box is checked.
Answers
-
You would need to add in a helper column on the source sheet such as a checkbox. In that column you would use a basic IF formula to check the box if the dates match.
Then your cross sheet formula would look at this new helper column and include the criteria for if the box is checked.
-
Thanks Paul, appreciate the quick response. It works!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!