Using COUNTIFS with INDEX MATCH

[torchedya]

I have a CSV file that is exported from a program that we use, which is then pasted into a sheet in our excel workbook. The issue is the CSV may have different headings in different columns depending on who downloaded it. So when I pull the spreadsheet the heading "Hostname" is in column D but when someone else does it the heading "Hostname" may be in column G. We use this spreadsheet to count how many devices we have deployed. I have figured out how to do a INDEX MATCH if I am pulling from only one heading:

=COUNTIF(INDEX('Sheet1'!$A$2:$AZ$5000,0,MATCH("Hostname",'Sheet1'!$A$1:$AZ$1,0)),"*pdu-mine*")

But some of my formulas currently pull from different columns and not just one for example:

=COUNTIFS('Sheet1'!A1:A5000,"*Virtual*','Sheet1'!L1:L5000,"*my house*"

Column A is "Type" and Column L is "Location". How can I use COUNTIFS for multiple headings that may move around on the sheet?

And if you are really looking for a challenge then how can I do it with a formula array that is already as complex as the below example:

{=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("*emu-mine*",'Sheet1'!D1:D5000)),MATCH('Sheet1'!D1:D5000,'Sheet1'!D1:D5000,0)),ROW('Sheet1'!D1:D5000)-ROW('Sheet1'!D2)+1),1))}

Thanks!!
[TABLE="width: 98"]
<tbody>[TR]
[TD][TABLE="width: 514"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]