Countif function with dynamic rows

0
votes

I would like to use the excel function Countif and give it a specific row number for it's range that is defined by the row number of a specific lookup value.

I wrote this formula, but it does not work. 

=COUNTIF(D(MATCH(A2;A:A;0)):M(MATCH(A2;A:A;0));"<>0")

How can I give a row number to my range?

2
excelexcel-formula
Welcome to SO. If a range is dynamic, it would be better to make it a named range, or a table. You can then refer to that instead. – GoodJuJu

2 Answers

2
votes

One way is to use OFFSET: something like this

=COUNTIF(OFFSET(D1,MATCH(A2,A:A,0),0,1,10),"<>0")
0
votes

You can also use the INDIRECT function here, which turns a string into a cell reference.

Also in the MATCH function, you should not let your search-value, 'A2' in this case, be a part of the search range 'A:A'. This creates a circular reference.

Something like this should work:

=COUNTIF(INDIRECT("D" & MATCH(B1;A:A;0) & ":M" & MATCH(B1;A:A;0));"<>0")