What is wrong with this COUNTIF formula?

[Alethia]

Hi,

I'm sure its a very obvious mistake I've made, but this is the firs
time I've attempted to use Excel.

I am typing in the following formula to find a percentage of a numbe
of values, but to ignore any values that are zero or less. The value
are changeable so it is vital that all cells that could be filled ar
considered. I used the COUNTIF function so only values greater tha
zero are considered.

This is what i entered:-

=SUM((F8,I8,L8,O8,R8,U8,X8,AA8)/800%)/COUNTIF(F8,I8,L8,O8,R8,U8,X8,AA8,">0")

It doesn't look right...i've tried without some brackets entering

=SUM(F8,I8,L8,O8,R8,U8,X8,AA8)/800%/COUNTIF(F8,I8,L8,O8,R8,U8,X8,AA8,">0")

But it still comes up with the "The formula you typed contains a
error" warning.

Help is very much appreciated!
Thanks a lot,
Alethi

 

[Frank Kabel]

Hi
try
=(SUM(F8,I8,L8,O8,R8,U8,X8,AA8)/8)/COUNTIF(F8,I8,L8,O8,R8,U8,X8,AA8,">0
")

 

[Pete JM]

Will this work?

=(SUM(F8,I8,L8,O8,R8,U8,X8,AA8)/800%)/COUNTIF(F8:AA8,">0")

or even

=(SUM(F8:AA8)/800%)/COUNTIF(F8:AA8,">0")


This will Sum and count all the cells in the range not just the one
you specified so it might not be what you are looking for.

Pet

 

[Alethia]

Thank you for your responses!

I tried Frank's solution and still the "The formula you typed contain
an error" warning appears...I really can't see why its not working
I've tried giving the range of cells a name and tried using this in th
formula and its still not working.

As to Pete's suggestion, it has to be that range of cells and not
series, unfortunately! Thank you for your suggestion though.

Any more suggestions?

Thanks a lot,

Alethi

 

[Frank Kabel]

Hi
sorry,
probelm is COUNTIF does not accepts this kind of ranges. try the
following instead:
=(SUM(F8,I8,L8,O8,R8,U8,X8,AA8)/8)/SUMPRODUCT((MOD(COLUMN(F8:AA8),3)=0)
*(F8:AA8>0))

 

[Dave Hawley]

Hi Alethia

The COUNTIF Function will only take a Contiguous range of cell, e.g
A1:K1 etc

You may need to use another row to assist. Not sure why the cells
between the Non-Contiguous cells must be exluded but I will assume for
the purpose of the example, they are larger than 100. In another row
place

=iF(AND(F8>0,F8<101),F8,"")

Enter this and copy across then use the new Contiguous range in a COUNT
function


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Pete JM]

The reason it is not working is because in a CountIF Fomula you specify
where to look and then what criteria to look for. Excel uses a comma to
seperate these arguments for example Where,What so with your formula
COUNTIF(F8,I8,L8,O8,R8,U8,X8,AA8,">0")

It is looking at the F8 as where and the I8 as what. So anything after
that is causing it to return an error.

Pete

 

[Alethia]

I can't get any suggestions to work!

I tried Dave's one but not sure how to continue the formula...it work
great for the cell F8 but i'm not sure how to add the other cells in t
that formula.

Is there something completely different i can try to achieve my aim
Have i explained the problem well enough?

<dreads handing in coursework

 

[Alethia]

Is this idea realistic?

If i were to create a column for number of tests completed by countin
the NUMBER of columns that have a percentage in them above 0%, and the
adding up the percentages then dividing them by the number o
percentages available....do you think that would do the job? I'm no
quite sure how to go about it but will start trying just in cas

 

[Dave Hawley]

Hi Alethia

Just copy my formula formula across to include all needed Columns.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Alethia]

Hi again Dave,

I'm really sorry to be of bother, but I'm not sure how to copy you
formula across the other columns...could you give me an example of wha
the formula would look like if i added on I8?
Thank you so much for your help, if very much appreciated