COUNTIF absolute value of cells in a range are > 0

D

Dave F

How would I use COUNTIF to count only those cells whose absolute values are
greater than 0?

This is my formula: =SUM(H5:H103)/COUNTIF(H5:H103,">0") But there are
negative numbers in the range which should be counted as well as the positive
numbers. I only want to exclude 0 values.

Thanks,

Dave
 
R

RagDyeR

Have you tried:

=SUM(H5:H103)/COUNTIF(H5:H103,"<>0")
?

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

How would I use COUNTIF to count only those cells whose absolute values are
greater than 0?

This is my formula: =SUM(H5:H103)/COUNTIF(H5:H103,">0") But there are
negative numbers in the range which should be counted as well as the
positive
numbers. I only want to exclude 0 values.

Thanks,

Dave
 
P

pinmaster

Hi Dave

Not sure what is the purpose of your post since you probably know the answer
but:

COUNTIF(H5:H103,"<>0")

hope I passed the test :)

Regards!
Jean-Guy
 
D

driller

by trying to figure out the complete formula u have
it looks clear u need an average function

=AVERAGE(IF((H5:H10<>0),(H5:H10<>0)*H5:H10))
hit ctrl-shft-enter

surely u know this especially when u want to consider a BLANK CELL as not
part of the count and has an If 0 value...data with text can be trapped since
the result will be an error - good time to fix the data..
regards
 
D

Dave F

The purpose of the post is that I was not thinking. Yes, the answer is
obvious.

Sorry about this.
 
P

pinmaster

Hi,

Blank cells and text do not affect an average formula so it could be
simplified to:

=AVERAGE(IF(H5:H103<>0,H5:H103))
enter using Ctrl+Shift+Enter


Regards!
Jean-Guy
 
D

driller

thats right again...
=AVERAGE(IF(H5:H103<>0,H5:H103))
with c-s-e
if strickly not need to count a text somewhere in the data, and without
bothering to check if there are some invalid data.
regards2,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top