Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

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

2 Answers 2

up vote 2 down vote accepted

My Answer:

=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)
share|improve this answer
    
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(
    INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH("Priority",1:1,0),4),"1","") & ":" &
             SUBSTITUTE(ADDRESS(1,MATCH("Priority",1:1,0),4),"1","")),
    1)

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).

enter image description here

share|improve this answer
    
+1 for being a complicated enough that I had to look at it for a few minutes to figure out why it worked. –  Daniel Cook Oct 2 '12 at 19:57
    
Haha, I think that's more of a -1 in my book. I was going to put in a bit more time and try to reduce it to a single instance of the I/S/A stuff, but yours is better than whatever I would have come up with. –  RocketDonkey Oct 2 '12 at 20:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.