PDA

View Full Version : Formula Question Please



gibbo1715
10-31-2006, 12:58 PM
Hi

I need to check a value and if it is not blank and does not contain 0 I need to count it, im trying the following but doesnt work for me, any one got any ideas please

Gibbo

=IF(Sheet1!AN5:AN100 <> 0,COUNT(Automated!AN5:AN100))

yngve
10-31-2006, 01:52 PM
try this
=IF(sum(Sheet1!AN5:AN100) <> 0,COUNT(sheet1!AN5:AN100))

yngve

Bob Phillips
10-31-2006, 01:57 PM
=COUNT(IF(AN5:AN100<>0,AN5:AN100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.

asingh
10-31-2006, 06:35 PM
True..the above is an array formula:


=COUNT(IF(AN5:AN100<>0,AN5:AN100))

You are comparing each value in the range to AN5:AN100 and verifying it for <> '0'. As XLD mentioned you will need to go to the formula and press control+shift+enter, in one go..and commit the formula. This will put curly braces around the formula so it looks like:



{=COUNT(IF(AN5:AN100<>0,AN5:AN100))}

asingh
10-31-2006, 06:37 PM
sincere apologies..for three same submits..something..kept hanging the browser..while I tried to submit..sorry for messing up this thread.... :(

regards,
Asingh

gibbo1715
11-01-2006, 10:06 AM
thankyou all

gibbo

lucas
11-01-2006, 11:12 AM
sincere apologies..for three same submits..something..kept hanging the browser..while I tried to submit..sorry for messing up this thread.... :(

regards,
Asingh
Don't worry about it...happens all the time. To most of us at some time or the other..