Referencing multiple worksheets with COUNTIFS

[BenHornsby2021]

Good morning!

I am trying to find a total for the number of referrals made each month to different members of our team using this formula:


=COUNTIFS(All!B3:B992,">="&"01/02/2021", All!B3:B992,"<"&"01/03/2021", All!G3:G992, "Chris")

At the moment, this gives an output of 2, as there are 2 cases on the All worksheet allocated to Chris.

I simply adjust this to show how many referrals each calendar month are made to 'Chris', by taking the data from an existing worksheet named 'All'.

I also want this total output from the COUNTIFS function to include the same date range and staff name from 2 other worksheets named ForClosure and CasesClosed respectively.

I tried this:

=COUNTIFS(All!B3:B992,">="&"01/02/2021", All!B3:B992,"<"&"01/03/2021", All!G3:G992, "Chris", ForClosure!B2:B992,">="&"01/02/2021", ForClosure!B2:B992,"<"&"01/03/2021", ForClosure!B2:B992, "Chris")

....but it gave an output of 0.

How do I change the syntax to include both worksheets, and ultimately up to 3 more?

[JohnTopley]

Try

Create named range "Sheets" with the sheet names [All, ForClosure, CasesClosed]

then use ..


=SUMPRODUCT(COUNTIFS(INDIRECT("'" &Sheets &"'!B3:B992"),">="&"01/02/2021", INDIRECT("'" & Sheets & "'!B3:B992"),"<"&"01/03/2021", INDIRECT("'" & Sheets&"'!G3:G992"), "Chris"))

I suggest you change the hard-coded dates to cell references e.g A1 , A2 containing your dates: similar for "name" (A3)

=SUMPRODUCT(COUNTIFS(INDIRECT("'" &Sheets &"'!B3:B992"),">="&A1, INDIRECT("'" & Sheets & "'!B3:B992"),"<"&A2, INDIRECT("'" & Sheets&"'!G3:G992"), A3))