Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function - using a date range
My formula is COUNTIF(M2:M999,"01/09/08,30/09/08")
I want it to count if the date in the cell is in any given month - the above isn't working. Can anyone suggest another way to add a date range as the criteria? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function - using a date range
Try this:
=SUMPRODUCT((MONTH(M2:M999)=9)*(YEAR(M2:M999)=2008 )) Hope this helps. Pete On Oct 2, 12:20*pm, Exceluser1 wrote: My formula is COUNTIF(M2:M999,"01/09/08,30/09/08") I want it to count if the date in the cell is in any given month - the above isn't working. Can anyone suggest another way to add a date range as the criteria? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function - using a date range
Hi E1,
Try this alternative way: =SUMPRODUCT(--(MONTH(M2:M999)=9)) Change the 9 for whatever month you want, or change it for a cell ref, and write the desired month number into that cell. Regards - Dave. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function - using a date range
Just a heads up, your formula will count empty cells as January if you try
to test for that month. If testing for January use: =SUMPRODUCT(--(MONTH(M2:M999)=9),--(M2:M999<"")) or =SUMPRODUCT(--(MONTH(M2:M999)=1),--(ISNUMBER(M2:M999))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dave" wrote in message ... Hi E1, Try this alternative way: =SUMPRODUCT(--(MONTH(M2:M999)=9)) Change the 9 for whatever month you want, or change it for a cell ref, and write the desired month number into that cell. Regards - Dave. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function - using a date range
One mo
=sumproduct(--(text(m2:m999,"yyyymm")="200809")) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =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 Exceluser1 wrote: My formula is COUNTIF(M2:M999,"01/09/08,30/09/08") I want it to count if the date in the cell is in any given month - the above isn't working. Can anyone suggest another way to add a date range as the criteria? -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function - using a date range
=SUMPRODUCT(--(TEXT(M2:M999,"mmm-yy")="Sep-08"))
"Exceluser1" wrote: My formula is COUNTIF(M2:M999,"01/09/08,30/09/08") I want it to count if the date in the cell is in any given month - the above isn't working. Can anyone suggest another way to add a date range as the criteria? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function - using a date range
Hi Sandy,
Thanks for pointing that out. So I tried entering zero into a cell with date format, and got 00-Jan-00. So that explains the month=1 bit, but the zeroth of January??? Dave. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function - using a date range
"
"Dave" wrote in message ... Hi Sandy, Thanks for pointing that out. So I tried entering zero into a cell with date format, and got 00-Jan-00. So that explains the month=1 bit, but the zeroth of January??? Dave. Yes it is strange. One minute past midnight on January 1st, (ie 1/1/1900 00:1 ), is 1.000694444 so everything under 1 must be in some sort of cyber time. Mind you that is where the *normal* times that you enter, (like 08:00) reside. Enter 08:00 four cells and then sum them in a cell formatted as dd/mm/yyyy hh:mm. You will get 01/01/1900 08:00. So where were the first three sets of eight hours? <g -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function - using a date range
As you say, some sort of cyber time, or perhaps just into the ether.
Regards - Dave In Perth, the current capital of Western Australia and not the crowning place of any kings that I know of. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function - using a date range
Hi all,
Solved my problem with the following sumproduct((M2:M999=CellA1)*(M2:M999<=cellA2)) where cell A1 is the start date and cell a2 is the end date. Had to do it this way because i am using the spreadsheet for more than one month and i need stats for each month. The data may be entered in say the September but it might not reach a conclusion for anything upto a year hence i have to keep it in the loop. Thanks for this new function I've just learnt I've also worked out how to get the product to cover more than one argument - yeahhh!! (Can you tell I'm new to this!) In case anyone is interested - I wanted to count the number of times a particular data set happened within any given month, but ONLY if they also missed a 5 day deadline - my solution is sumproduct(((M2:M999=CellA1)*(M2:M999<=CellA2))*( N2:N999=cellB1)*(N2:N999<=cellB2)) the first part is the selector for a given month, the second if its over 5 days but under 365 days So far it seams to work with test data - can anyone see any flaw in this function argument - please try to keep your answers for idiots as I'm still getting used to what some of the terms actually mean! cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif with date range criteria | Excel Worksheet Functions | |||
How do I put a date range in the criteria of a countif formula? | Excel Discussion (Misc queries) | |||
countif date range | Excel Worksheet Functions | |||
SumProduct/CountIf dilemna + Date Range | Excel Worksheet Functions | |||
use a date range as criteria in a countif formula | Excel Worksheet Functions |