Help Please
 

Can someone give me a formula to extract the month from a date entry and
count the total number of times this is an enrty onto another sheet. e.g.
01/01/2006, I need to extract the middle 01 and have it in as a total under
Jan on another sheet, Can anyone tell me a formula to do this, I have tried
everything that I know of anyone any ideas????

Mark

Help Please
 

try
=SUMPRODUCT((MONTH(Sheet5!C2:C22)=6)*1)

--
Don Guillett
SalesAid Software

"santaviga" wrote in message
...
Can someone give me a formula to extract the month from a date entry and
count the total number of times this is an enrty onto another sheet. e.g.
01/01/2006, I need to extract the middle 01 and have it in as a total
under
Jan on another sheet, Can anyone tell me a formula to do this, I have
tried
everything that I know of anyone any ideas????

Mark

Help Please
 

Hi this works but if I set it to recognise the month 1 for Jan it counts all
entries and also empty cells, have you any ideas how to combat this, I
appreciate your Help.

Regards,

Mark

"Don Guillett" wrote:

try
=SUMPRODUCT((MONTH(Sheet5!C2:C22)=6)*1)

--
Don Guillett
SalesAid Software

"santaviga" wrote in message
...
Can someone give me a formula to extract the month from a date entry and
count the total number of times this is an enrty onto another sheet. e.g.
01/01/2006, I need to extract the middle 01 and have it in as a total
under
Jan on another sheet, Can anyone tell me a formula to do this, I have
tried
everything that I know of anyone any ideas????

Mark

Help Please
 

Limit to the number of rows with dates c 8 or c10 or ?


--
Don Guillett
SalesAid Software

"santaviga" wrote in message
...
Hi this works but if I set it to recognise the month 1 for Jan it counts
all
entries and also empty cells, have you any ideas how to combat this, I
appreciate your Help.

Regards,

Mark

"Don Guillett" wrote:

try
=SUMPRODUCT((MONTH(Sheet5!C2:C22)=6)*1)

--
Don Guillett
SalesAid Software

"santaviga" wrote in message
...
Can someone give me a formula to extract the month from a date entry
and
count the total number of times this is an enrty onto another sheet.
e.g.
01/01/2006, I need to extract the middle 01 and have it in as a total
under
Jan on another sheet, Can anyone tell me a formula to do this, I have
tried
everything that I know of anyone any ideas????

Mark

Help Please
 

=SUMPRODUCT(--isnumber(sheet5!c2:c22),--(MONTH(Sheet5!C2:C22)=1))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

santaviga wrote:

Hi this works but if I set it to recognise the month 1 for Jan it counts all
entries and also empty cells, have you any ideas how to combat this, I
appreciate your Help.

Regards,

Mark

"Don Guillett" wrote:

try
=SUMPRODUCT((MONTH(Sheet5!C2:C22)=6)*1)

--
Don Guillett
SalesAid Software

"santaviga" wrote in message
...
Can someone give me a formula to extract the month from a date entry and
count the total number of times this is an enrty onto another sheet. e.g.
01/01/2006, I need to extract the middle 01 and have it in as a total
under
Jan on another sheet, Can anyone tell me a formula to do this, I have
tried
everything that I know of anyone any ideas????

Mark




--

Dave Peterson

Help Please
 

use this
=SUMPRODUCT(--(MONTH(C1:C100)=1),--ISNUMBER(C1:C100))

--
Don Guillett
SalesAid Software

"santaviga" wrote in message
...
Hi this works but if I set it to recognise the month 1 for Jan it counts
all
entries and also empty cells, have you any ideas how to combat this, I
appreciate your Help.

Regards,

Mark

"Don Guillett" wrote:

try
=SUMPRODUCT((MONTH(Sheet5!C2:C22)=6)*1)

--
Don Guillett
SalesAid Software

"santaviga" wrote in message
...
Can someone give me a formula to extract the month from a date entry
and
count the total number of times this is an enrty onto another sheet.
e.g.
01/01/2006, I need to extract the middle 01 and have it in as a total
under
Jan on another sheet, Can anyone tell me a formula to do this, I have
tried
everything that I know of anyone any ideas????

Mark

Help Please
 

Works a treat, thanks a million

Much appreciated

Mark


"Don Guillett" wrote:

use this
=SUMPRODUCT(--(MONTH(C1:C100)=1),--ISNUMBER(C1:C100))

--
Don Guillett
SalesAid Software

"santaviga" wrote in message
...
Hi this works but if I set it to recognise the month 1 for Jan it counts
all
entries and also empty cells, have you any ideas how to combat this, I
appreciate your Help.

Regards,

Mark

"Don Guillett" wrote:

try
=SUMPRODUCT((MONTH(Sheet5!C2:C22)=6)*1)

--
Don Guillett
SalesAid Software

"santaviga" wrote in message
...
Can someone give me a formula to extract the month from a date entry
and
count the total number of times this is an enrty onto another sheet.
e.g.
01/01/2006, I need to extract the middle 01 and have it in as a total
under
Jan on another sheet, Can anyone tell me a formula to do this, I have
tried
everything that I know of anyone any ideas????

Mark