• 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 Function

G

GN0001

Member
We have a range in A1 to A6

1

2

3

4

1

2

I use countif function and enter the function as this

=Countif($A$1:$A$6,$A1) and it gives me the value.

If I enter the function as =Countif($A$1:$A$6,$A$1:$A$6)and enter it as an array formula. it only copies one value to all cells.


On the second instance the formula says that I have two arrays:


1 1

2 2

3 3

4 4

1 1

2 2


In term of an array formula, how could this be interpreted? How could we compare an array with a range? Which element of the first array and which element of second array are compared?


Thank you very much for the help in explaining this.

Guity
 
A few points here Guity

A Range and an Array are effectively treated the same way in Excel

Each item in the Range or Array has a position ie: 1st, 2nd 3rd etc and so the 2nd item of a range is comparable to the 2nd item of an array etc


For your 1st equation =Countif($A$1:$A$6,$A1)

You are asking Excel to Count the number of times the value in A1 appears in the Range A1:A6


In your second equation =Countif($A$1:$A$6,$A$1:$A$6) you are asking Excel to count the number of times the values in each cell in A1:A6 appears in the range A1:A6

Problem is that it is only displaying the result for the first value of the answer array, which is the same as the first equation.

You haven't given Excel anywhere to put the 2nd to 6th answers

Try this

Put the array equation into B1,

Now select B1:B6

Press Edit (F2) and Ctrl Shift enter

you will now have 2, 2, 1, 1, 2, 2 in column B1:B6


So what happening


In B1 you are getting the first value of the array

In B2 you are getting the second value of the array

In B3 you are getting the third value of the array

etc


Now if you wanted to add up all those values

you could use =sum(Countif($A$1:$A$6,$A$1:$A$6)) Ctrl Shift Enter
 
Hui,


Thank you for the answer. Now everything is clear. Do you know how we can access our old posts? Because I have something from you and I can't find it. Thank you for sum and countif function at the end, that is a great help.


High Regards,

Guity
 
Click on your name Guity N underneath the avatar to the left of your last post
 
You must log in or register to reply here.
Back
Top