Countif using the min function

[bsnapool]

Hi All

Come stuck on a formulae and hoping some one might be able to help me out.

I am trying to count numeric values within a range, and then bring back the smallest number, so here is what i have so far:

=COUNTIF('1'!$F12:$L12,">0")

the problem I am having is that if for e.g. 5, 5, 5 is entered within the range, it counts this as three times, but i would only like the formulae to count it once no matter how many numeric values are entered.

Hope this makes sense.

Thanks

Andrew

[Bob Phillips]

=SUMPRODUCT((F12:L12>0)/COUNTIF(F12:L12,F12:L12&""))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"bsnapool" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All
>
> Come stuck on a formulae and hoping some one might be able to help me
> out.
>
> I am trying to count numeric values within a range, and then bring back
> the smallest number, so here is what i have so far:
>
> =COUNTIF('1'!$F12:$L12,">0")
>
> the problem I am having is that if for e.g. 5, 5, 5 is entered within
> the range, it counts this as three times, but i would only like the
> formulae to count it once no matter how many numeric values are
> entered.
>
> Hope this makes sense.
>
> Thanks
>
> Andrew
>
>
> --
> bsnapool
> ------------------------------------------------------------------------
> bsnapool's Profile:
http://www.excelforum.com/member.php...o&userid=36115
> View this thread: http://www.excelforum.com/showthread...hreadid=559862
>

[bsnapool]

Thanks for your reply, but this formulae adds the different variations of the data entered. I would just like numeric values to be counted as 1 occurence.

For example

m - 5.5
t - 6.6
w - 9.6
t JURY SERVICE
f - JURY SERVICE

I would just like the formulae to count this as once occurence, even though 3 days have been entered.

Hope this makes sense

Thanks

Andrew

[Bob Phillips]

Try this then

=SUMPRODUCT(--ISNUMBER(F12:L12),(F12:L12>0)/COUNTIF(F12:L12,F12:L12&""))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"bsnapool" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for your reply, but this formulae adds the different variations
> of the data entered. I would just like numeric values to be counted as
> 1 occurence.
>
> For example
>
> m - 5.5
> t - 6.6
> w - 9.6
> t JURY SERVICE
> f - JURY SERVICE
>
> I would just like the formulae to count this as once occurence, even
> though 3 days have been entered.
>
> Hope this makes sense
>
> Thanks
>
> Andrew
>
>
> --
> bsnapool
> ------------------------------------------------------------------------
> bsnapool's Profile:
http://www.excelforum.com/member.php...o&userid=36115
> View this thread: http://www.excelforum.com/showthread...hreadid=559862
>