CODEWITHSUNDEEP
excel
daneshjai
daneshjai

Reputation: 878

COUNTIFS with range criteria on different sheets

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.

Upvotes: 2

Views: 47118

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 5

Related Questions