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