|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 24th, 2010, 12:53 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
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? 
|
|
#2
March 24th, 2010, 01:15 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count a cell in month format
Try
=SUMPRODUCT((MONTH(C1:C100)=3)*(D1  100="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?
|
|
#3
March 24th, 2010, 01:27 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 100="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?
|
|
#4
March 24th, 2010, 01:36 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 21="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?
|
|
#5
March 24th, 2010, 02:01 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 21="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?
|
|
#6
March 24th, 2010, 02:56 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count a cell in month format
Oops. I missed that. Try out this non-array formula...
=SUMPRODUCT((TEXT(C1:C100,"mmmyyyy")="MAR2010")*(D 1 100="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 100="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? .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
