# Count based on month and Year

• Hi Guys

Got this problem

I want to count all values in column B that is = to the value in H2 but is must check column C for the month and the year (I1 (month) and F1 (year) )

I attach a sample file hope it makes more sense.

forum.ozgrid.com/index.php?attachment/54393/

## Files

• Re: Count based on month and Year

Try:

=SUMPRODUCT(--(\$B\$2:\$B\$6=\$H2),--(TEXT(\$C\$2:\$C\$6,"mmm yyyy")=I\$1&" "&\$F\$1))

copied down and across.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Count based on month and Year

you will need to change the values for the year and the months - to actual dates
and then format to

MMM - to just display the Month
YYYY - to display the year

=COUNTIFS(B2:B6,H2,C2:C6,">="&DATE(YEAR(F1),MONTH(I1),DAY(1)),C2:C6,"<="&DATE(YEAR(F1),MONTH(I1),DAY(31)))

ETAF

• Re: Count based on month and Year

Thank NBVC work perfectly.

• Re: Count based on month and Year

NBVC

I want to extend the code if possible.

I want to add a column with Yes and No values and check first for Yes and then do the rest.

• Re: Count based on month and Year

Hi, sorry for the long wait...meetings....

=SUMPRODUCT(--(\$D\$2:\$D\$6="Y"),--(\$B\$2:\$B\$6=\$H2),--(TEXT(\$C\$2:\$C\$6,"mmm yyyy")=I\$1&" "&\$F\$1))

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Count based on month and Year

Thx NBVC works like a charm.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!