Search code examples
excelreferenceexternalcriteria

Referencing cells in a different workbook using countif

I'm trying to use the countif function to count how many employees complete training late. The required by date and all the completion dates are recorded on a separate workbook.

I know the usual forumula is =countif(range, criteria) and have no issues when I type an actual required by date for the criteria. The issue has to do with referencing a cell in a seperate workbook. The formula looks like this: =countif(xxxxG37:G158,>xxxxG5) with xxxx being the location of the external workbook.
The formula works just fine when I put an actual date (i.e >xxxxG5 = 11/6/17).

The issue has to deal with how I"m referencing the criteria cell.

Any help would be appreciated. Thanks! The data looks as follows:

G5  11/6/17

G37  11/2/17
G38  11/3/17
G39  11/9/17
G40  11/10/17
G41  11/1/17

G5 is the required by date. G37-G41 are the completion dates. I want to count how many of the dates are after 11/6/17.

Solution

  • Here is a typical syntax:

    =COUNTIF([b.xlsx]Sheet1!$G$37:$G$41,">" & [b.xlsx]Sheet1!$G$35)

    enter image description here

    where the formula appears in another workbook (a.xlsx)