• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif - as an Array and not as an Array

L

leimst

Member
Hello,


As an example, if I have the following data in A2:A15,

65

54

25

36

59

36

12

65

84

87

65

36

36

25

If I use Countif($A$2:$A$15) I get the result of 4 but if I execute the Countif as an Array formula, I get 3 as a result. Can anyone explain why this is the case?


Thanks for any help!


Leimst
 
your formula is missing the 2nd argument, so we can't really see what it is you are doing. Can you post both of your formulas?
 
Hi Leimst,

When I used the formula and data set you posted, I get the value 3 (regardless of how the formula is used -- array or normal usage).


Is there more to your question?


-Sajan.
 
I'm getting Countif($A$2:$A$15,$A$2:$A$15) = 4 and {Countif($A$2:$A$15,$A$2:$A$15)} = 3 on my spreadsheet. However, when I select the Countif($A$2:$A$15,$A$2:$A$15) cell and hit the F2 key and then the F9 key and click on the checkmark in the formula bar it returns 3 just like the Array formula does. I don't understand how the same formula (Countif($A$2:$A$15,$A$2:$A$15)) can give me 2 different results depending on whether I hit F2, F9 and click on the checkmark in the formula bar or not. That should not affect how the formula calculates should it?


Leimst
 
I have shuffled the numbers around while experimenting with this. Perhaps the result of 4 it was showing was from a previous configuration of the data? I would think that it would recalculate in real time though as I made changes? I am not in Manual calc mode.


Leimst
 
leimst,

I am unable to duplicate your results. Using the Evaluate Formula command, it appears that when you pass the whole range in a regular formula, or when in an array formula, the COUNTIF just takes the first cell and uses that as a criteria, thus always giving an answer of 3. Please advise if are able to get the answer 4 somehow.
 
Hi, leimst!


I've copied your data set and your formulas, non-array and array, and in both cases I get a 3 displayed. As Luke M stated rightly only the first value (65) is taken into consideration for the count, and it appears three times.


Regards!
 
I have recreated the scenario in a new spreadsheet and cannot replicate the issue...I am consistently getting the result of 3 as Luke and SirJB7 have experienced. It is still occurring in my original spreadsheet though so I'm going to clear it out and start over.


Thanks for the help!


Leimst
 
You must log in or register to reply here.
Back
Top