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

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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,
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> > >
>
>
>