Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula / Formatting Help - please!"
Hi,
In a workbook Cell G23 is a data box with a dd-mmmm-yy format. On execution of a macro this data (and lots more) is copy/pasted to another worksheet. Cell G23 is copy/pasted to next avaiable row in column B. I need to be able to count the amont of times each different month is shown in column B, but the pasted data is in the formatt dd-mmmm-yy and my count formula is just looking for the 'month' name. I have tried formatting the whole of column B to become just mmmm but this doesn't work. Here is the formula that I am using to do the inital 'count' =SUMPRODUCT((B3:B100="JULY")*(C3:G100<"")) EG when I paste the date given by the user as 21July05 into the cell required I need this cell to show JUST 'July' so that it will be counted! Any ideas/help ?? many thanks |
#2
|
|||
|
|||
Hi Anthony,
Assuming that the data in range [B3:B100] are real dates and not text (if you can change their look by formatting then it is probable the former), you could do one of the following (keeping in mind that formatting itself doesn't change the cell value, so no mattaer what format you apply to a cell that initially showed 21-July-05 the value of the cell remains 38554): =SUMPRODUCT((MONTH(B3:B100)=7)*(C3:G100<"")) or (much less efficient) =SUMPRODUCT((UPPER(TEXT(B3:B100,"mmmm"))="JULY")*( C3:G100<"")) Regards, KL "Anthony" wrote in message ... Hi, In a workbook Cell G23 is a data box with a dd-mmmm-yy format. On execution of a macro this data (and lots more) is copy/pasted to another worksheet. Cell G23 is copy/pasted to next avaiable row in column B. I need to be able to count the amont of times each different month is shown in column B, but the pasted data is in the formatt dd-mmmm-yy and my count formula is just looking for the 'month' name. I have tried formatting the whole of column B to become just mmmm but this doesn't work. Here is the formula that I am using to do the inital 'count' =SUMPRODUCT((B3:B100="JULY")*(C3:G100<"")) EG when I paste the date given by the user as 21July05 into the cell required I need this cell to show JUST 'July' so that it will be counted! Any ideas/help ?? many thanks |
#3
|
|||
|
|||
KL.
Many thanks - easy when you know how !! rgds Anthony "KL" wrote: Hi Anthony, Assuming that the data in range [B3:B100] are real dates and not text (if you can change their look by formatting then it is probable the former), you could do one of the following (keeping in mind that formatting itself doesn't change the cell value, so no mattaer what format you apply to a cell that initially showed 21-July-05 the value of the cell remains 38554): =SUMPRODUCT((MONTH(B3:B100)=7)*(C3:G100<"")) or (much less efficient) =SUMPRODUCT((UPPER(TEXT(B3:B100,"mmmm"))="JULY")*( C3:G100<"")) Regards, KL "Anthony" wrote in message ... Hi, In a workbook Cell G23 is a data box with a dd-mmmm-yy format. On execution of a macro this data (and lots more) is copy/pasted to another worksheet. Cell G23 is copy/pasted to next avaiable row in column B. I need to be able to count the amont of times each different month is shown in column B, but the pasted data is in the formatt dd-mmmm-yy and my count formula is just looking for the 'month' name. I have tried formatting the whole of column B to become just mmmm but this doesn't work. Here is the formula that I am using to do the inital 'count' =SUMPRODUCT((B3:B100="JULY")*(C3:G100<"")) EG when I paste the date given by the user as 21July05 into the cell required I need this cell to show JUST 'July' so that it will be counted! Any ideas/help ?? many thanks |
#4
|
|||
|
|||
Maybe you should test the year as well
=SUMPRODUCT(--(MONTH(B3:B100)=7),(YEAR(B3:B100)=2005),--(C3:G100<"")) or =SUMPRODUCT(--(TEXT(B3:B100,"mmmyyyy"))="Jul2005"),--(C3:G100<"")) which might be less efficient, but much more? It has one less function call, one less test, but is comparing strings, but I doubt you could measure it unless you have thousands of formulae. -- HTH Bob Phillips "Anthony" wrote in message ... KL. Many thanks - easy when you know how !! rgds Anthony "KL" wrote: Hi Anthony, Assuming that the data in range [B3:B100] are real dates and not text (if you can change their look by formatting then it is probable the former), you could do one of the following (keeping in mind that formatting itself doesn't change the cell value, so no mattaer what format you apply to a cell that initially showed 21-July-05 the value of the cell remains 38554): =SUMPRODUCT((MONTH(B3:B100)=7)*(C3:G100<"")) or (much less efficient) =SUMPRODUCT((UPPER(TEXT(B3:B100,"mmmm"))="JULY")*( C3:G100<"")) Regards, KL "Anthony" wrote in message ... Hi, In a workbook Cell G23 is a data box with a dd-mmmm-yy format. On execution of a macro this data (and lots more) is copy/pasted to another worksheet. Cell G23 is copy/pasted to next avaiable row in column B. I need to be able to count the amont of times each different month is shown in column B, but the pasted data is in the formatt dd-mmmm-yy and my count formula is just looking for the 'month' name. I have tried formatting the whole of column B to become just mmmm but this doesn't work. Here is the formula that I am using to do the inital 'count' =SUMPRODUCT((B3:B100="JULY")*(C3:G100<"")) EG when I paste the date given by the user as 21July05 into the cell required I need this cell to show JUST 'July' so that it will be counted! Any ideas/help ?? many thanks |
#5
|
|||
|
|||
Bob,
Thanks - that will come in handy as my data goes into next year rgds Anthony "Bob Phillips" wrote: Maybe you should test the year as well =SUMPRODUCT(--(MONTH(B3:B100)=7),(YEAR(B3:B100)=2005),--(C3:G100<"")) or =SUMPRODUCT(--(TEXT(B3:B100,"mmmyyyy"))="Jul2005"),--(C3:G100<"")) which might be less efficient, but much more? It has one less function call, one less test, but is comparing strings, but I doubt you could measure it unless you have thousands of formulae. -- HTH Bob Phillips "Anthony" wrote in message ... KL. Many thanks - easy when you know how !! rgds Anthony "KL" wrote: Hi Anthony, Assuming that the data in range [B3:B100] are real dates and not text (if you can change their look by formatting then it is probable the former), you could do one of the following (keeping in mind that formatting itself doesn't change the cell value, so no mattaer what format you apply to a cell that initially showed 21-July-05 the value of the cell remains 38554): =SUMPRODUCT((MONTH(B3:B100)=7)*(C3:G100<"")) or (much less efficient) =SUMPRODUCT((UPPER(TEXT(B3:B100,"mmmm"))="JULY")*( C3:G100<"")) Regards, KL "Anthony" wrote in message ... Hi, In a workbook Cell G23 is a data box with a dd-mmmm-yy format. On execution of a macro this data (and lots more) is copy/pasted to another worksheet. Cell G23 is copy/pasted to next avaiable row in column B. I need to be able to count the amont of times each different month is shown in column B, but the pasted data is in the formatt dd-mmmm-yy and my count formula is just looking for the 'month' name. I have tried formatting the whole of column B to become just mmmm but this doesn't work. Here is the formula that I am using to do the inital 'count' =SUMPRODUCT((B3:B100="JULY")*(C3:G100<"")) EG when I paste the date given by the user as 21July05 into the cell required I need this cell to show JUST 'July' so that it will be counted! Any ideas/help ?? many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula using Date Formatting | Excel Discussion (Misc queries) | |||
conditional formatting with formula | Excel Worksheet Functions | |||
Conditional Formatting formula not acceptable? | Excel Discussion (Misc queries) | |||
Formula for Formatting Fonts | Excel Discussion (Misc queries) | |||
Concatenation formula loses text wrap formatting | Excel Discussion (Misc queries) |