Sheets Formula - ArrayFormula and CountIf

0
votes

Could not find a suitable solution, hence this post.

Have 2 sheets - Attendance & Payroll where attendance is filled in a pivoted manner (see sample).

For a given date range, I want to count the number of "Absent" days for the staff. The Non-Array-Formula (in Payroll column "Absent") below does that. Note: column A with staff ids is a dynamic list even though its fixed in the sample.

How this formula works:

  1. match the payroll-staffid to the attendance column-header-staffid using MATCH
  2. date range given in cells payroll B1,B2
  3. Settings!$B$13 contains the columnar range as per (2)
  4. OFFSET (3) by MATCH to get the staff attendance
  5. COUNTIF the number of "Absent" entries in staff attendance range - CORRECT
  6. ArrayFormula does NOT work when the payroll-staffid "A5" is changed to "A5:A15"

Note: there is no guarantee that payroll-staffids order and attendence-header-staffids are both in same order -> that's why each staffid is mapped MATCHed and OFFSET. 

=COUNTIF(OFFSET(INDIRECT(Settings!$B$13),0,MATCH(A5,Attendance!$B$1:$1,FALSE)),"Absent")

Sample sheet here.

1
google-sheets

1 Answers

0
votes
=ArrayFormula(VLOOKUP(A5:A15, TRANSPOSE({INDIRECT(AttHeader,FALSE);MMULT(TRANSPOSE(SIGN(ROW(INDIRECT(AttUnitMatrix)))),IF(INDIRECT(AttData,FALSE)="Absent",1,0))}),2,FALSE))

See linked sample sheet in OP. For defined names; see the Settings sheet. All ranges are computed separately to reduce the size of the formula.

1) Start operating in "block mode", ignoring order of staff-ids. "AttData" is the string representation of the data block and mapped to 1 if "Absent" else 0.

IF(INDIRECT(AttData,FALSE)="Absent",1,0)

2) This matrix is multiplied by a unit row matrix from range string "AttUnitMatrix"

TRANSPOSE(SIGN(ROW(INDIRECT(AttUnitMatrix))))

3) MMULT returns a row of "Absent" counts

4) { } is used to prepend the staff-ids to the "Absent" counts for a 2 row matrix.

{INDIRECT(AttHeader,FALSE);MMULT(...)}

5) TRANSPOSE result to be accessed by VLOOKUP (2 column matrix)

6) VLOOKUP takes care of out of order staff-ids by matching the key-staff-ids to the generated row matrix of (staff-id / absent-count) pairs.

fireworks ... pat on my back :)

In this case and others, and I've sent feedback to Google about this, a feature request "Named Formulas" akin to "Named Ranges", to be used in standard formulas. This is WITHOUT resorting to GAS. When formulas become large, this is NOT a luxury, but a NECESSITY. If readers find such a feature useful, please send feedback to Google. 

eg: UnitMatrix($1) => TRANSPOSE(SIGN(ROW(INDIRECT($1))))
MMULT(UnitMatrix(AttUnitMatrix),IF(INDIRECT(AttData,FALSE)="Absent",1,0))