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

Count the number of multiple date on the same column

W

Winston618

Member
Hi,

I would like to write VBA that lists out the number (column A) of multiple date on the same column (column B) from a worksheet. Since the number of date may vary, I am having a hard time to get it down.

I know how to get the total number of all the date, but I can't count the number by date.

Below is the desire result I would like to get, thanks in advance.

擷取1.PNG
 
Hi ,

Can you not use an Excel worksheet formula for this ?

=COUNTIF(A$2:A2 , A2)

will return the count of each date.

Copying this down will give you what you want.

Narayan
 
It works so well!
Can you explain a bit about the logic behind this?
I have a hard time understanding it.
Thanks again!

NARAYANK991 said:
Hi ,

Can you not use an Excel worksheet formula for this ?

=COUNTIF(A$2:A2 , A2)

will return the count of each date.

Copying this down will give you what you want.

Narayan
Click to expand...
 
Hi ,

The COUNTIF function has two parameters ; the first one is a range over which the counting is to be done , and the second one is a value which will be looked for in the range.

As a simple example , suppose you enter the numbers 1 through 10 in the range A2:A11 ; now , in B1 if you enter the formula :

=COUNTIF(A2:A11 , ">5")

you will see 5 displayed. What this means is that in the range A2:A11 , there are 5 values which are bigger than 5.

The formula =COUNTIF(A2:A11 , "<3") will return the result 2 , since only two values are less than 3.

This function is normally used to count for duplicates ; thus , given the same data of values 1 through 10 entered in the range A2:A11 , the formula :

=COUNTIF(A$2:A$11 , A2)

will return 1 , since there is only one occurrence of the value in A2 within the range A2:A11.

You can copy this formula down from B2 to the other cells B3:B11 , and all of them will display 1.

Changing any of the values in the range A2:A11 to include duplicates will reflect in the count for that value.

We have used absolute referencing for the range A$2:A$11 ; now suppose we use relative referencing for the end-point , as in :

=COUNTIF(A$2:A2 , A2)

What this means is that we are seeing how many occurrences of the value in A2 are present in the range A$2:A2 ; obviously , this will return the result 1.

When we copy this formula down , the formula changes as follows :

Copied to B3 , the formula will become =COUNTIF(A$2:A3 , A3)

Copied to B4 , the formula will become =COUNTIF(A$2:A4 , A4)

and so on.

Thus , as duplicates are encountered down the range , the count will reflect the occurrence number ; the second occurrence will be numbered 2 , the third occurrence will be numbered 3 and so on.

Narayan
 
Winston618 said:
It works so well!
Can you explain a bit about the logic behind this?
I have a hard time understanding it.
Thanks again!
Click to expand...
Hope this will help you:
To count based on multiple criteria using OR logic, you can use the COUNTIFS function with an array constant. By default, the COUNTIFS function applies AND logic. When you supply multiple conditions, all conditions must match in order to generate a count
 
You must log in or register to reply here.
Back
Top