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.
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.
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)))
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....
Anyway, assuming your column is D, just add the new condition:
=SUMPRODUCT(--($D$2:$D$6="Y"),--($B$2:$B$6=$H2),--(TEXT($C$2:$C$6,"mmm yyyy")=I$1&" "&$F$1))
Re: Count based on month and Year
Thx NBVC works like a charm.
Don’t have an account yet? Register yourself now and be a part of our community!
