Mr Excel & excelisfun Trick 68: Count Dates in Column That Match Year and Month Criteria

[earthworm82]

I tried in different approach . i kept the formula much easy to understand and simple

this is what i did .

=SUMPRODUCT(--(TEXT($A$7:$A$21­0,"YYY")&TEXT($A$7:$A$210,"MMM­")=A4&B4))

in this i dont need to create seperate cell or any other complicated countif

[ExcelIsFun]

EXCELlent!!!!

Cool solution!

[kokanee007]

ExcellsFun, I can't tell you how much I've learned from your videos! Thanks so much. I think this solution works using SUMPRODUCT. =SUMPRODUCT((A2=YEAR(A6:A11))*­(MONTH(1&B2)=MONTH(A6:A11)))

Basically the Month(1&B2) is the key because this converts a text month into a numeric month.

[ExcelIsFun]

That is amazing!!!! I had no idea that 1&FEB inside month = 2. Totally cool!!

But SUMPRODUCT & TEXT means you just type two functions.

I wonder which is faster calculating?? SUMP & TEXT or SUMP & YEAR & MONTH?

[denisrapp]

yep i wont work like that,however it will if we change the month dropdown from months-words to months-numbers...but that is not as per the "dueling rules" I guess)

Thanks again!

[ExcelIsFun]

Yes, you are right! If the Month Cell was a number your formula would work, This video was about what to do if you have words for months - which is the case sometimes in cross tabulated reports.

[denisrapp]

great video, thanks a lot!

I got one more variant with boolean logic that might work:

=sumproduct((month(A7:A210)=B4­)*(year(A7:A210)=A4))

but we need to convert months to numbers...

[ExcelIsFun]

That won't work becasue the month criteria is a word and the MONTH functions spits out a number...

[krn14242]

I like Mike's TEXT formula, but I'm into the fancy formulas. thanks kevin

[ExcelIsFun]

I am glad that you liked it!

[10Shlomi10]

Shalom mike and Bill,

Relly great to learn from you guys!

A Geart FUN !

what about VBA ?

will you teach this "fild" too?

alraedy waiting for your next excel tricks....

Shlomi

[ExcelIsFun]

I am not good with VBA, so I do not teach that. I will have some new Excel Tricks soon!