excel formula range reference using column number

Question

I'm looking for a way to use the column number that I get from MATCH in a range reference. I need to count the number of X's in this row, up to a specific point. Here's some code as an example of what I'm trying to get to work.

=COUNTIF($A$14:MATCH($E$1| $A$13:$V$13)$14|"X")

I need to use MATCH (or something equivalent), as it lets me know the number of columns I need to count across. Users may or may not remember to clear out the whole row past this number of columns even though it should only count, say, 10 columns' worth of X's in this row. I already know exactly how many columns to count due to other data on the sheet (referenced as $E$1 in match) that matches a number in the check row, so something along these lines is a preferable solution.

barry houdini · Accepted Answer · 2013-04-04 14:01:50Z

1

You can use INDEX with MATCH to get the end of your range, e.g.

=COUNTIF($A$14:INDEX($14:$14,MATCH($E$1,$A$13:$V$13)),"X")

So if the MATCH function gives you 10, for example, that will count "x"s in A14:J14

Note that if you want an exact MATCH you need zero as third argument of MATCH function (I haven't included that)

answered Apr 4, 2013 at 14:01

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel formula range reference using column number

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related