formula or code?

[Guest]

is there a way to check all cells in a column that have an entry > 0 then
subtract 10 from each cell >10 for the total. Some cells will have a entry of
0
column goes from k4:k99 k100 is total for column. New total is necessary to
tell difference from income and entry fees for parade.
Thanks in advance
Ya All are a big help

 

[Jim Cone]

=SUM(K4:K99)-(COUNTIF(K4:K99,">10")*10)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Curt"
<[email protected]>
wrote in message
is there a way to check all cells in a column that have an entry > 0 then
subtract 10 from each cell >10 for the total. Some cells will have a entry of
0
column goes from k4:k99 k100 is total for column. New total is necessary to
tell difference from income and entry fees for parade.
Thanks in advance
Ya All are a big help

 

[Guest]

Thank you Jim

 

[Guest]

didn'i quite make it total came out 810 from 930 should of been 680 there
were 25 entries that needed 10 subtracted from
maybe if I see difference needed then I will understand how countif works.
Am lost at the *10 at end. I tried -10 no good
Thanks

 

[Jim Cone]

COUNTIF(K4:K99,">10") counts the number of entries with
a value greater than 10. That should equal 25 in your worksheet.
25 * 10 = 250 (the amount to subtract)

You can test it by entering... =COUNTIF(K4:K99,">10") in a separate
cell and see what you get. Also try... =COUNTIF(K4:K99,"<=10")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Curt" <[email protected]>
wrote in message
didn'i quite make it total came out 810 from 930 should of been 680 there
were 25 entries that needed 10 subtracted from
maybe if I see difference needed then I will understand how countif works.
Am lost at the *10 at end. I tried -10 no good
Thanks

 

[Guest]

skip my last post my lack of knowledge and to quick to respond. Need >= to
and all worked ok
Thanks again
Curt