countif

F

fzl2007

How do I modify =COUNTIF($E$2:$E$250,J2) so that 250 is dynamic which
will be the row count with data?
 
I

isabelle

hi,

if there are no empty cell in the range E2:Ex

=COUNTIF(OFFSET($E$2,,,COUNTA($E:$E)),J2)

otherwise an array formula (validate with ctrl+maj+enter)

=COUNTIF(INDIRECT("E2:E"&MAX(IF(NOT(ISBLANK(E2:E65536)),ROW(E2:E65536)))),J2)
 
I

isabelle

a bit shorter

array formula (validate with ctrl+maj+enter)
=COUNTIF(INDIRECT("E2:E"&MATCH(9^9,E:E)),J2)

--
isabelle



Le 2012-04-06 11:56, isabelle a écrit :
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

countif 1
Calculate the median value before a specified list 0
Counting Multiple Text Cells 4
Using <= in countifs statement 1
Finding the weighted average of text that have been given values 1
sumproduct for numeric range 3
Count times that are more than 5 minutes in a range from a newworksheet 1
countif with multiple criteria 3

Top