Countif help

[kanddo2]

I have a document that has a range of values. I want to be able to complete a countif scenario where I can count all the values less than the median (varies as you move from result sets). I have to do this across several categories, so I would like to paste this formula without having to adjust the criteria manually each time. I already have the value of the median for the range in a cell.

For example one range is H100:H343. The median forumla result is in J343.

I would like something that would work like this:

=countif(H100:H343,<J343)<J343)<J343)<J343)<J343)< p>
This doesn't work because it has to be a hardcoded criteria value in the countif formula. It won't accept the cell reference.

Also tried:

=COUNT(IF(H100:H343,j343,h100:h343,))<?xml:namespace prefix = j343,H343 /><j343,H343:h343,))< p>
Any advice would be helpful. Using 2007, but have to save to xls format for most users.
</j343,H343:h343,))<>

 

[Colin Legg]

Hi,

The formulas in your post got cut off.

Did you try either of these?

=COUNTIF(H100:H343,"<"&J343)

=COUNTIF(H100:H343,"<"&MEDIAN(H100:H343))

HTH
Colin

 

[kanddo2]

I tried to edit my post after seeing it cut off after I subtmitted, but it wouldn't update for some reason.

Either solution works perfectly. I had failed to put the & in. I knew I missing something but couldn't remember what it was.

Thanks.