5

In Google Sheets I have a table like this:

JOB             TYPE       STATUS
Generate files  Scheduled  In Progress
Update certs    Manual     Completed
Copy files      Scheduled  Pending
... (hundreds of rows)

Rows are sorted by descending priority. I need to count the STATUS values of the top 20 “Scheduled” jobs:

Top 20 Scheduled Jobs: Completed:   2
                       In Progress: 4
                       Pending:     14

I can use FILTER to select all the “Scheduled” jobs, but how do I select the “top 20” rows from that result (meaning the first 20 rows after the filter has been applied)?

  • From the table you gave, I'm not able to prioritize the scheduled jobs. How do you select the top 20 rows? – Jacob Jan Tuinstra Dec 29 '14 at 14:54
  • I simplified things for this example, where the rows themselves are already sorted by descending priority. The prioritization itself wasn't the issue here -- it was how to select the first 'n' rows out of the filtered results. ARRAY_CONSTRAIN() was the solution. – ChipW Dec 30 '14 at 3:06
4
+50

UNTESTED. Please try:

=countif(array_constrain(filter(C:C,B:B="Scheduled"),20,1),E2)  

and cop down where E2, E3 and E4 contain Completed, In Progress and Pending respectively.

  • 2
    ARRAY_CONSTRAIN() was the solution I needed, and your code showed how to use that result set. Thanks. – ChipW Dec 30 '14 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.