| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
