COUNTIFS with range criteria on different sheets

Question

I have not read if there is a limitation with COUNTIFS function, but I am experiencing the following:

I want to count the number the number of times two conditions are true in a row. For example

Sheet 1
USERID    DATE
SAM        12/1/2014
SAM        12/3/2014
SAM        12/4/2014
JON        12/3/2014
BOB        11/5/2014

Sheet 2
Dates to match against {12/3/2014, 12/4/2014, 12/5/2014}

Sheet 3
USERID   DATECount
SAM       2
JON       1
BOB       0

If all this information is in the same sheet the following formula works (Assuming data in Sheet 1 is in columns A and B, and the data in Sheet 2 is in the first row D to F, and the data in Sheet 3 starts at D5.

=COUNTIFS(A:A,D5,B:B,"="&$D$1:$F$1)

Currently, to get over this, I have a SUM function and a COUNTIFS for each criteria in the range for criteria 2. But it's pretty ugly since there are 20 criteria.

What I trying to find is a more elegant way to do this, or if there is another function that can return the same results.

Note that the date range can change every month.

Not sure I understand. For a start, the formula =COUNTIFS(A:A,D5,B:B,"="&$D$1:$F$1) will not operate over the range of values in D1:F1, since there is nothing to coerce it to do so. In fact, that formula, as it stands, will only ever operate over the first cell in that range, i.e. D1. As such, there is no difference whatsoever between that formula and =COUNTIFS(A:A,D5,B:B,"="&$D$1). — XOR LX

Unknown Unknown · Accepted Answer · 2014-11-25T08:58:29

You need a SUMPRODUCT wrapper that will iterate the COUNTIFS results through the cells in Sheet2!$D$1:$F$1.

COUNTIFS Multiple worksheets

The formula in Sheet3!E5 is,

=SUMPRODUCT(COUNTIFS(Sheet1!A:A,D5,Sheet1!B:B,Sheet2!$D$1:$F$1))

Fill down as necessary.

COUNTIFS with range criteria on different sheets

1 Answers