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)?

Improve this question
2
  • From the table you gave, I'm not able to prioritize the scheduled jobs. How do you select the top 20 rows?
    – Jacob Jan
    Commented Dec 29, 2014 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
    Commented Dec 30, 2014 at 3:06

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 2
    ARRAY_CONSTRAIN() was the solution I needed, and your code showed how to use that result set. Thanks.
    – ChipW
    Commented Dec 30, 2014 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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