CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulacountif
Brian - RGY Studio
Brian - RGY Studio

Reputation: 525

Expanding a countif function down a column in Google Sheets

I'm trying to expand the following formula down a column using an array, which I understand doesn't work exactly with countif. I need this as the divisor in the column where I have the numerator expanding successfully down the column. Here is the function that works fine in each row:

=arrayformula(countifs(unique(if('All Data'!$B$2:$B=$A2,'All Data'!$A$2:$A,)),">="&edate(F$2,0),unique(if('All Data'!$B$2:$B=$A2,'All Data'!$A$2:$A,)),"<="&edate(F$2,1)))

I am trying to avoid copying the formulas all over the sheet, as it is rather large and would prefer it to work as an array where it gives me the count for a2 in row 2, a3 in row 3, a4 in row 4, etc, so I tried this formula, which doesn't work:

=arrayformula(countifs(unique(if('All Data'!$B$2:$B=$A2:$A,'All Data'!$A$2:$A,)),">="&edate(F$2,0),unique(if('All Data'!$B$2:$B=$A2:$A,'All Data'!$A$2:$A,)),"<="&edate(F$2,1)))

Is there a workaround I can use to get this expanding for me?

Thank you!

Upvotes: 0

Views: 435

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

You could accomplish this with an Apps Script custom function. To achieve this, follow these steps:

  • In your spreadsheet, select Tools > Script editor to open a script bound to your file.
  • Copy this function in the script editor, and save the project:
function AVERAGE_STUDENTS(classes, monthIndex) {
  const sheet = SpreadsheetApp.getActive().getSheetByName("All Data");
  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  const monthRows = data.filter(row => row[0].getMonth() + 1 == monthIndex);
  return classes.map(classy => {
    const classRows = monthRows.filter(row => row[1] == classy[0]);
    const totalStudents = classRows.length;
    const uniqueDates = new Set(classRows.map(element => JSON.stringify([element[0], element[1]]))).size;
    return totalStudents / uniqueDates;
  });
}
  • Now, if you go back to your spreadsheet, you can use this function like any in-built one. You just have to provide the appropriate range for the classes (in this case it would be A3:A18) and the month index (for April, that's 4), as you can see here:

enter image description here

Note:

  • This function could also be modified in order to just call the function at C1 and populate all months automatically.

Reference:

Upvotes: 1

Related Questions