Search code examples
google-sheetsarray-formulas

Count items in column range if value exists in corresponding row ranges

I have a spreadsheet which tracks weekly meeting attendance. I need to return the number of individuals who attended at least one meeting in the month, not the sum total of weekly meeting attendees. In other words, if a person attended 4 meetings in the month, the count is incremented by 1, not 4.

Names are listed in Column A, and the weeks in the month are listed in columns B-F (e.g. B2 is "Sep 2"; C2 is "Sep 9"; "D2 is "Sep 16", and so on.) When a person attends a meeting, the corresponding cell receives an "X".

So far, the only method I know I can use to return the number of unique or distinct meeting attendees is to first use a set of formulas in one column (H) to return whether an "X" is found in the corresponding rows, and then a second formula that references the range (in column H) containing the first set of formulas to return the number of TRUE results.

What I'm trying to do is use an ArrayFormula or something similar to give me the final number in just one shot. I'm currently using a COUNTIF function on values in a column range while the rows in that very range are populated using COUNTA functions.

How can I use just one formula to return the attendance count - not depending on that intermediary step/range in column H?

I can't seem to get an array formula to work correctly, and I haven't been able to find similar answers despite hours of searching. Apologies if there are similar questions already posted (I couldn't find one asking quite the same question as mine). Here's my best attempt so far:

=ArrayFormula(COUNTIF(COUNTA(B3:F17) > 0,TRUE)) ...which returns 1.

Here is an example spreadsheet with sample data.

enter image description here

Solution

  • In I22 I entered this formula

    =countif(ArrayFormula(countif(if(B3:F17="X", row(B3:B17)), row(B3:B17))), ">0")

    the formulas in H3:H17 are not used in this formula.

    See if that works?