Search code examples
excelexcel-formulaexcel-2013countif

COUNTIFS across two sheets

I am trying to use a COUNTIFS statement in Excel (version 2013) in order to count the number of rows on a separate sheet that are between a date range specified in another. The result is returning 0, when in fact it should be 19.

=COUNTIFS('Sheet1'!P:P,">Sheet2!B2",'Sheet1'!P:P,"<Sheet2!D2")

Sheet1 Column P is a the given date for each row in Sheet1, and Sheet2 B2 and D2 are the start and end dates that I want to filter between.

Ideally, I would like to add the ability to filter the same date range on a second column of Sheet1 and third criteria. "Count if the date range is between X and Y and Column A contains (Z)"

Solution

  • Please try: 

    =COUNTIFS(Sheet1!A:A,"Z",Sheet1!P:P,">"&Sheet2!B2,Sheet1!P:P,"<"&Sheet2!D2)

    since the cell references are qualified with sheet names, should work in either sheet (or elsewhere!) but might be shortened if in one or other of Sheet1 and Sheet2.