count "x" by Month

  • Thread starter Thread starter Annette
  • Start date Start date
A

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
 
Use:

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

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

David Hager
Excel FMVP
 
Annette,

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

Dan E
 
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 :)
 
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!
 
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 :)
Click to expand...

But only a tad more involved.

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