How to count multiple columns

CLBai

Hi, I am trying to count the number of users whose activity is greater than 0 for the past 3 months combined. Is there a way to use COUNTIFS on multiple columns?

Anson Cheung

Yes.

=COUNTIFS([Column2]:[Column2], >0, [Column3]:[Column3], >0, [Column4]:[Column4], >0)

CLBai

That's just giving me a 0.

Paul Newcome

Is there additional criteria you need to be able to filter on such as a specific user, or are you just trying to get the number of cells in those three columns that are greater than zero?

If just getting the number of cells greater than zero:

=COUNTIFS([1st Column]:[3rd Column], @cell> 0)

Andrée Starå

Hi @CLBai

I hope you're well and safe!

You'd use multiple countifs instead. Countifs formula + countifs formula and do that for each column. (if all columns are beside each other, you could one COUNTIFS with ColumnNameFirst:ColumnNameLast)

Make sense?

Did that work?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

CLBai

I want to count if the users activity is greater than 0 but I only want to count each user once. Like if they were active in Dec, Jan, and Feb, I only want to count them once. If I add all countifs together, it counts some people more than once.

Paul Newcome

Ah. Ok. In that case the easiest way to do this would be to insert a helper column and use a formula to check a box if there is at least one column on each row that is greater than zero.

=IF(MAX([1st Column]@row:[3rd Column]@row)> 0, 1)

Then you can count how many boxes are checked per person.