Share via

Counting the number of rows in an external closed workbook

Anonymous
2023-10-13T16:18:36+00:00

Hello,

I am trying to create a formula which will grab some data from another spreadsheet called 'Active.xlsx'.

I need it to count all rows in which the following is true:

  • column K has "1st Line Support" in it
  • column C has a date which is more than or equal to 5 days ago

I cannot use COUNTIF or SUMIF because neither of these work on a closed workbook.

This is the closest I have found to something that seems like it should work:

=SUMPRODUCT(([Active.xlsx]Sheet1!$K:$K="Applications Support") - ([Active.xlsx]Sheet1!$K:$K="Applications Support", [Active.xlsx]Sheet1!$C:$C >= (TODAY() - 5)))

However Excel does not accept it.

Is there a solution?

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2023-10-13T23:09:30+00:00

    Hi,

    Does this work?

    =SUMPRODUCT(([Active.xlsx]Sheet1!$K:$K="Applications Support")*([Active.xlsx]Sheet1!$C:$C >= TODAY() - 5))

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2023-10-13T16:50:58+00:00

    Sum
    =SUM(FILTER([Active.xlsx]Sheet1!$J:$J,([Active.xlsx]Sheet1!$K:$K="Applications Support")*([Active.xlsx]Sheet1!$C:$C>=TODAY()-5)))

    .

    Count

    =SUM(IF([Active.xlsx]Sheet1!$K:$K="Applications Support",1,0)*IF([Active.xlsx]Sheet1!$C:$C>=TODAY()-5,1,0))

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments