Count a cell in month format
 

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?

Count a cell in month format
 

Try
=SUMPRODUCT((MONTH(C1:C100)=3)*(D1:D100="reg"))

--
Jacob


"Mike Wheeler" wrote:

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?

Count a cell in month format
 

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.

--
Best Regards,

Luke M
"Jacob Skaria" wrote in message
...
Try
=SUMPRODUCT((MONTH(C1:C100)=3)*(D1:D100="reg"))

--
Jacob


"Mike Wheeler" wrote:

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?

Count a cell in month format
 

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.


"Mike Wheeler" wrote:

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?

Count a cell in month format
 

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!

"Mike H" wrote:

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.


"Mike Wheeler" wrote:

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?

Count a cell in month format
 

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

=SUMPRODUCT((TEXT(C1:C100,"mmmyyyy")="MAR2010")*(D 1:D100="reg"))


--
Jacob


"Luke M" wrote:

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.

--
Best Regards,

Luke M
"Jacob Skaria" wrote in message
...
Try
=SUMPRODUCT((MONTH(C1:C100)=3)*(D1:D100="reg"))

--
Jacob


"Mike Wheeler" wrote:

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?


.