Making COUNTIF More Adaptable

[AR1987]

I'm trying to count how many times a name occurs in a specific column. However, depending on where the person sits in the hierarchy, their name will appear in a different column.

How do I make the "criteria range" in a COUNTIF function dynamic?

I need a way to reference a key cell in the criteria range of COUNTIF. I will change this cell for each report depending on where in the hierarchy the individual is. I need the COUNTIF function to switch columns based on the value in this key cell.

Or is there simply a better way to go about counting these instances?

Note: If it matters, I'm using countifs, but only one part of the formula needs to adapt as described above.

[wsthost]

One way is to combine concatenate with indirect.
Indirect works as follows:
assume cell A1's value is the number 10 and cell B2 has a value of "A1"
in cell C3, if you have a formula =B2, then it will simply display "A1" since it references cell B2
however, if in cell C3, you do =indirect(B2), then the output will say 10 since indirect reads the value of B2 as a A1 cell reference and boots you over there

Concatenate works by combining values together
if in A1 you have XYZ
and in A2 you have ABC
and in A3 you say =A1&A2, then the value is XYZABC
one can use the concatenate function as well, but that's horribly inefficient.

now, combining these together for your specific question:
1) assume the lookup column for your COUNTIF is either column B or C
2) assume you have the logic in another cell somewhere that determines if the lookup column should be B or C

then, you do the following:
1) have your logic that determines column B or C in a cell that isn't in column B or C, say in D1
2) D1's output will be either B:B or C:C to indicate entire column; you can do this via an IF statement or some other logic (we don't usually like IF statements)
3) in place of the lookup column in your COUNTIF, instead of a hard-coded reference, put in: indirect(D1) and you should be all set!