COUNTIFS with dynamic range when same data is in different column in different sheets

[Symphony044]

Hi excel experts,

I hope you can help..

I have multiple exports from a task management system.
In each export the header&data for 'Escalated to IT' is in a different column.

In each sheet I entered the formula to return the cell which has the words 'Escalated to IT' in it, then removed chars leaving just column letter.

Now I want to write COUNTIFS formula which inserts the column letter in the formula.

Many thanks!!

 

[Fluff]

Hi & welcome to MrExcel.
Can you post the countifs formula.

 

[Symphony044]

Thank you so much

=COUNTIFS('Project Name'!BN:BN,"yes",'Project Name'!D:D,"In Range",'Project Name'!F:F,"FALSE",'Project Name'!G:G,"FALSE",'Project Name'!H:H,"FALSE",'Project Name'!I:I,"FALSE",'Project Name'!J:J,"FALSE",'Project Name'!K:K,"FALSE")

All the cell ranges vary between the different exports. What's in D in one export can be in BH in another.

 

[Fluff]

Thanks for that, how about

=COUNTIFS(index('Project Name'!A:ZZ,xmatch("Escalated to IT",'Project Name'!A:ZZ)),,"yes",'Project Name'!D:D,"In Range",'Project Name'!F:F,"FALSE",'Project Name'!G:G,"FALSE",'Project Name'!H:H,"FALSE",'Project Name'!I:I,"FALSE",'Project Name'!J:J,"FALSE",'Project Name'!K:K,"FALSE")

 

[Symphony044]

Thanks for sending it. Unfortunately it did not work

 

[Fluff]

Oops missed a comma out & forgot to remove another, it should be

=COUNTIFS(index('Project Name'!A:ZZ,,xmatch("Escalated to IT",'Project Name'!A:ZZ)),"yes",'Project Name'!D:D,"In Range",'Project Name'!F:F,"FALSE",'Project Name'!G:G,"FALSE",'Project Name'!H:H,"FALSE",'Project Name'!I:I,"FALSE",'Project Name'!J:J,"FALSE",'Project Name'!K:K,"FALSE")

 

[Symphony044]

Hi Fluff,

Thanks but it didn't work, so I am trying a different direction. I am separating the formula into separate cells referencing the cells I want and then concatenate into one cell creating a formula, hopefully it will work..

 

[Fluff]

Thanks but it didn't work

It would help if you said in what way it didn't work.
But I have spotted another error, try

=COUNTIFS(index('Project Name'!A:ZZ,,xmatch("Escalated to IT",'Project Name'!A1:ZZ1)),"yes",'Project Name'!D:D,"In Range",'Project Name'!F:F,"FALSE",'Project Name'!G:G,"FALSE",'Project Name'!H:H,"FALSE",'Project Name'!I:I,"FALSE",'Project Name'!J:J,"FALSE",'Project Name'!K:K,"FALSE")

 

[Symphony044]

Hi Fluff, it worked something, I have to double check but very optimistic, thanks!!

 

[Fluff]

You're welcome & thanks for the feedback.