CODEWITHSUNDEEP
excelexcel-formula
Sunkanx
Sunkanx

Reputation: 1

Countif function with dynamic rows

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?

Upvotes: 0

Views: 958

Answers (2)

Marco Vos
Marco Vos

Reputation: 2968

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

Upvotes: 0

Charles Williams
Charles Williams

Reputation: 23505

One way is to use OFFSET: something like this

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

Upvotes: 2

Related Questions