# MS Excel: How to work with a column chosen by value in header (row1)

I am importing a large dataset from a website and I want to build some summary stats on a new sheet. The column order is not fixed, so I want to be able to (for example) count all the cells with the value of "1" in the column with header "priority". I know that `MATCH("Priority", 1:1)` will give me the column number, but how do I use that in a `COUNT` formula?

-
At least the answers are complicated enough that I am not embarrassed by how easy it was! –  Justin Oct 2 '12 at 23:10

``````=COUNTIF(OFFSET(\$A:\$A,0,MATCH("Priority",1:1)-1),1)
``````

Now to explain:

`Offset(\$A:\$A,0,X)` returns the entire column starting with the first offset by `X` where in this situations X = `MATCH("Priority",1:1) - 1`.

Because `MATCH("Priority",1:1)` return the column number, we must subtract 1 to get the proper offset.

Then the last bit `,1)` is indicating we are looking for the value of 1.

Hope that's sufficientlly clear so you can make the rest of the ones you need.

Incase it isn't, all you should have to change is replace `"Priority"` with whatever you want to search, and the last `1` with whatever you are looking for, and this should return the count.

Just for fun, this would also work:

``````=COUNTIF(INDEX(1:1048576,0,MATCH("Priority",1:1)),1)
``````
-
Awesome answer - definitely use this one over mine :) –  RocketDonkey Oct 2 '12 at 19:52
I agree, this seems more robust and I can substitute other formulas for the `COUNTIF` when I need other forms of data (some of the columns may need to be averaged, for example). Thanks! –  Justin Oct 3 '12 at 13:50

Here is a messy way you could do it that involves the `ADDRESS` and `INDIRECT` formulas (sample data assumed to be in `A1:B6`:

``````=COUNTIF(
Basically what it does is finds the column matching `Priority` in your headers (here it is column `B`), finds the `ADDRESS` (in this case, `B1`), converts the column number into a letter (`SUBSTITUTE`) and then converts it into an actual range (`INDIRECT`).