Count a cell in month format

  • Thread starter Thread starter Mike Wheeler
  • Start date Start date
M

Mike Wheeler

Cell data:
15-Feb REG
22-Feb REG
8-Mar REG
15-Mar REG
15-Mar REG
29-Mar APIN
29-Mar REG
29-Mar REG
29-Mar REG
29-Mar APIN
29-Mar APIN
5-Apr SHOP
12-Apr APIN
12-Apr APIN
26-Apr SHOP
Pulled NSTA
Pulled NSTA
Pulled NSTA
Pulled SHOP
Pulled SHOP
Pulled NSTA


The above columns are C and D, and I am trying to force A1 to give me a
count when column C is in the month of March, and D = "reg". I have a macro
that filters C and D by the selected macro, so the month value will need to
change according to the macro. I tried COUNT, and other versions, but it
doesn't like that.

Help?
 
The MONTH function will return an error when it tries to process that later
cells containing "Pulled". Could try this array* formula Mike:

=SUM((MONTH(IF(ISNUMBER(A1:A21),A1:A21,0))=3)*(B1:B21="reg"))

Note that the "=3" part is the section that control which month you are
looking at.
*Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.
 
Hi,

The non-dates in the date range give a problem with a standard sumproduct so
try this

=SUMPRODUCT((ISNUMBER(FIND("Mar",TEXT(C1:C21,"mmm"))))*(D1:D21="Reg"))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Ah, this is the one that worked. THANK YOU. Now I just need to try and
format a cell to base the month off of instead of creating 12 macros, one for
each month. But that is easy.

Thanks!
 
Oops. I missed that. Try out this non-array formula...

=SUMPRODUCT((TEXT(C1:C100,"mmmyyyy")="MAR2010")*(D1:D100="reg"))
 
Back
Top