Formula / Formatting Help - please!"

[Guest]

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

 

[KL]

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

 

[Guest]

KL.
Many thanks - easy when you know how !!
rgds
Anthony

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

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

 

[Bob Phillips]

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

KL.
Many thanks - easy when you know how !!
rgds
Anthony

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

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

 

[Guest]

Bob,
Thanks - that will come in handy as my data goes into next year
rgds
Anthony

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

KL.
Many thanks - easy when you know how !!
rgds
Anthony

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


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