count "x" by Month

[Annette]

I have X spread out in column B and the dates found in column K ... I want
to add the number of X's found in a particular month. (Using Office2000)

Does someone have a formula they can share?

Thanks!

Annette

 

[David Hager]

Use:

=SUM((B_range="X")*(MONTH(K_range)="Y"))

Hold down the Ctrl and Shift keys when entering this formula.

David Hager
Excel FMVP

 

[Dan E]

Annette,

Sample for counting May
=SUMPRODUCT((MONTH(K1:K100)=5)*(B1:B100="X"))
to count other months change the 5

Dan E

 

[Dave R.]

Maybe I am mis reading her question, but I interpreted it as she wanted to
count the number of X for each month since Xs are "spread out" in the column
(whatever that means). So any column B could have "X" or "XXX" or "xxxxx" in
it.. making the formula a tad more involved. only she will tell for sure tho
if she returns

 

[Annette]

Umm ... ya .. I guess I'm not real hot at explaining things. Loved the
answer though (I found I could use it in something else!), but for now I'm
going with D. Hager's answer.

Thanks!

 

[Harlan Grove]

Maybe I am mis reading her question, but I interpreted it as she wanted to
count the number of X for each month since Xs are "spread out" in the column
(whatever that means). So any column B could have "X" or "XXX" or "xxxxx" in
it.. making the formula a tad more involved. only she will tell for sure tho
if she returns

But only a tad more involved.

=SUMPRODUCT(--(MONTH(K1:K100)=5),LEN(B1:B100)-LEN(SUBSTITUTE(B1:B100,"X","")))