Count dates in a column by year and Month

[Naveen N]

Dear Reader,

I have column A (date&time) and Column B (number 1,2,3,4,5).

I have need count the dates based on Column A and Number by CoLumn By MONTH and YEAR.
I have specified the format in Column E to I. Please find enclosed excel.

Please suggest the how do we get the count.

Regards,
Naveen N

 

[Nebu]

Hi:

Is this what you are looking for?

Thanks

 

[Naveen N]

HI,

yes, but I am looking to achieve i using formulas.

Thanks for the quick response.

regards,
Naveen N

 

[Nebu]

Hi:

With formula.Array formula use control+shift+enter key to execute.
Thanks

 

[Naresh Yadav]

Dear Naveen,

if you are ready to add helper column to get your desired result than you can try this one..

=COUNTIFS($B$2:$B$46,$E2,$C$2:$C$46,F$1)

 

[xlstime]

Hey Naveen,

please use below formula -

cell f2 = =SUMPRODUCT((TEXT($A$2:$A$46,"mmm-yy")=TEXT(F$1,"mmm-yy"))*($B$2:$B$46=$E2))

and drag.

HI,

yes, but I am looking to achieve i using formulas.

Thanks for the quick response.

regards,
Naveen N

 

[Naveen N]

HI,

The array formula solves my query.
But a questions: in count if why do we need $A$2:$A$46 at the end
=COUNT(IF(($B$2:$B$46=$E2)*(MONTH($A$2:$A$46)=MONTH(F$1))*(YEAR($A$2:$A$46)=YEAR(F$1)),$A$2:$A$46))

Regards,
Naveen N

 

[NARAYANK991]

Hi ,

In the formula :

=COUNT(IF(($B$2:$B$46=$E2)*(MONTH($A$2:$A$46)=MONTH(F$1))*(YEAR($A$2:$A$46)=YEAR(F$1)),$A$2:$A$46))

the IF function has 3 parameters , out of which only 2 have been used.

The first parameter , coloured BLUE , is the condition check ; the second parameter , coloured RED , is the output if the condition check returns TRUE.

Thus , the formula will count those entries in the range $A$2:$A$46 which satisfy the criteria given by :

($B$2:$B$46=$E2)*(MONTH($A$2:$A$46)=MONTH(F$1))*(YEAR($A$2:$A$46)=YEAR(F$1))

This will return an array of 0s and 1s , 0 where any of the conditions is not satisfied , and 1 where all of the conditions are satisfied.

The IF function will then return the value from the range $A$2:$A$46 for each 1 , and FALSE for each 0.

The COUNT function will ignore the FALSE values and count the rest.

Narayan

 

[bines53]

Hello friends

It is also possible that,

=SUMPRODUCT(--(MONTH($A$2:$A$46)=MONTH(F$1))*(YEAR($A$2:$A$46)=YEAR(F$1))*($B$2:$B$46=$E2))

David

 

[Naveen N]

Thanks all for the inputs