COUNTIFS: Column Formula pulling from 2 Columns on 2 Sheets
Hello!
I've built some COUNTIFS in the past and I've always struggled, but I've also always managed to get it in the end. I'm trying to build a column formula that pulls data from 4 different columns between two different sheets. What I've come up with so far is coming back with UNPARSEABLE.
=COUNTIFS({Reviewer 1}, Associate@row), OR(COUNTIFS({Project Reviews Range 2}, Associate@row)
^I tested just the first half and it pulled the correct amount/updated when I added more as it should, but the second I try adding the OR option, it seems to not like it. I've tried with with both a new COUNTIFS and without. There was one way that it seemed like it was working, but the information from the 2nd half was overriding all the information from the 1st half instead of adding to it. If anybody has any clue as to what I'm doing wrong or could throw out suggestions, that would be great! Especially given that I still have to add another entire part to this to count the other sheet on top of this as well.
Thanks in advance!
Best Answer
-
I was finally able to get it to work (across multiple sheets). I think the trick was just changing it from an "OR" to a "+" and treating them all as their own formula in one long strand
=COUNTIFS({Reviewer 1}, Associate@row) + COUNTIFS({Project Reviews Range 2}, Associate@row) + COUNTIFS({Project Reviews Archive Range 1}, Associate@row) + COUNTIFS({Project Reviews Archive Range 2}, Associate@row)
1st Sheet: =COUNTIFS({Reviewer 1}, Associate@row) + COUNTIFS({Project Reviews Range 2}, Associate@row)
2nd Sheet: + COUNTIFS({Project Reviews Archive Range 1}, Associate@row) + COUNTIFS({Project Reviews Archive Range 2}, Associate@row)
Answers
-
What exactly are you wanting to accomplish. You cannot have references to two separate sheets within the same function, so you may need to do two separate COUNTIFS and then add them together.
-
I'm just wanting to count two columns and have it count by the person's name in the column. So essentially, if my name is in either of the two columns, then it should count 1.
-
I was finally able to get it to work (across multiple sheets). I think the trick was just changing it from an "OR" to a "+" and treating them all as their own formula in one long strand
=COUNTIFS({Reviewer 1}, Associate@row) + COUNTIFS({Project Reviews Range 2}, Associate@row) + COUNTIFS({Project Reviews Archive Range 1}, Associate@row) + COUNTIFS({Project Reviews Archive Range 2}, Associate@row)
1st Sheet: =COUNTIFS({Reviewer 1}, Associate@row) + COUNTIFS({Project Reviews Range 2}, Associate@row)
2nd Sheet: + COUNTIFS({Project Reviews Archive Range 1}, Associate@row) + COUNTIFS({Project Reviews Archive Range 2}, Associate@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!