SOLVED

Countif function

Copper Contributor

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

@JMB17 

 

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

@GrahamA1010 

 

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.

 

JMB17_0-1625063409988.png

 

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)

 

@JMB17 

 

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. 

Sorry, but prediction is outside my realm.
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@GrahamA1010 

 

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.

 

JMB17_0-1625063409988.png

 

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)

 

View solution in original post