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

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

--
HTH

Bob Phillips

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

"bsnapool" wrote in
message ...

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

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

Try this then

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

--
HTH

Bob Phillips

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

"bsnapool" wrote in
message ...

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