COUNTIFS: Column Formula pulling from 2 Columns on 2 Sheets

Jacob Gress

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!

Jacob Gress

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)

Paul Newcome

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.

Jacob Gress

https://community.smartsheet.com/discussion/comment/341407#Comment_341407

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.

Jacob Gress

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)