 SOLVED

# Countif function

I want to count the number of times "0" is found in 10 individual cells, not in a range. What is the formula ?

5 Replies

# Re: Countif function

You could try:
=INDEX(FREQUENCY((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19),{-1E-25,0}),2)

# Re: Countif function

Thanks the formula =index(frequency((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19(,{-1E-25,0}),2) worked well. However when finding the same value subsituting "0" for "1", "2" and "3" respectively did not work.

Could you explain the formula and suggest the correct formula for find the number of times 1,2 and 3 occur in the 10 cells.

Why use index and not just frequency? What is the significance of {-1e-25,0),2). I am assuming the 0 is the value we are seeking to know.

Thanks

best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Countif function

Frequency will return an array with an element count 1 more than the bins_array argument (number of elements within the braces { }).

I included -1E-25 as I wasn't sure if your data included negatives or decimals. So, -1E-25 should, I believe, group everything below 0. Then, the 0 element should capture the 0's as well as everything between it and the next element in the bins_array, but since it is the last element the frequency function automatically groups everything greater than it into another element (and is why the function always returns an array 1 element greater than the number of elements in bins_array).

In this example, you can see frequency is returning 3 elements, everything below zero, zero, everything above zero. So, index is used to pull out the second element. If you're just dealing with integer data, then you should be able to count the 1's with:

=INDEX(FREQUENCY((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19),{0,1}),2)

If decimal data, then I believe you could use:

=INDEX(FREQUENCY((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19),{0.999999999999999,1}),2)

# Re: Countif function

Thank you very helpful.

What do you know about prediction, a series of whole numbers either a "0" or a "1" , can we predict what the next number will be, will it be a "0" or will it be a "1".

I would love hear your thoughts.

# Re: Countif function

Sorry, but prediction is outside my realm.