Multiple INDIRECT within a COUNTIFS

[malaki1974]

Hello and thank you for looking at this.

I have just started using INDIRECT and am finding it very challenging. I have a couple of use cases. One I have solved, this new one is killing me on syntax.
I am using a dropdown to change a year. When it changes my formula updates to reference a new tab with same rows/columns. Works great.
But I have a similar formula that references the variable sheet name twice and I cannot figure out how to edit to make it work.

=COUNTIF(INDIRECT("'"&B2&"'!$F$2:$F$100"),B20) // works great: b2 is the year/name of sheet, b20 is the name that should be counted in the column F

Here is the troublesome one (it works but I want to also replace sheet explicit name with INDIRECT as in first part of formula

=COUNTIFS(INDIRECT("'"&B2&"'!$F$2:$F$117"),B20,'2021'!D2:D117,D15) // same as above but find the Product type (d15).

How to drop in INDIRECT("'"&B2&"'??? in place of the '2021' ?

Thank you!

 

[Fluff]

How to drop in INDIRECT("'"&B2&"'??? in place of the '2021' ?

Exactly the same way that you did for col F

 

[malaki1974]

Thanks for responding! I tried it before to no avail, hence the post. But I tried it again (see below). The error is also included.

=COUNTIFS(INDIRECT("'"&B2&"'!$F$2:$F$117"),B17,INDIRECT("'"&B2&"'!D2:D117",D15))

Error:

Error COUNTIFs expects all arguments after position 2 to be in pairs

 

[Fluff]

You almost have it, just one of the closing brackets in the wrong place, try

=COUNTIFS(INDIRECT("'"&B2&"'!$F$2:$F$117"),B17,INDIRECT("'"&B2&"'!D2:D117"),D15)

 

[malaki1974]

MrExcel has never let me down. Thank you so much for your assistance.

 

[Fluff]

You're welcome & thanks for the feedback.