Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count days in excel
I am trying to use countif to count the number of times within a month that
my fire department has a call, meeting, truck check. I have the dates in mm/dd/yy format. I figure that all I would have to do is use =COUNTIF (A1:A25, 11/*/09) and it would work. But that is not the case. when I add more calls/meetings/truck checks it does not add the new one to the total. And yes I did change A25 to A26 so that it new to look there also. But the total won't change. What am I doing wrong? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count days in excel
One way...
=SUMPRODUCT(--(MONTH(A1:A25)=n)) Where n = the month number from 1 to 12. Note that empty cells will evaluate as month number 1 (Jan). To account for that: =SUMPRODUCT(--(A1:A25<""),--(MONTH(A1:A25)=n)) -- Biff Microsoft Excel MVP "firemedic1203" wrote in message ... I am trying to use countif to count the number of times within a month that my fire department has a call, meeting, truck check. I have the dates in mm/dd/yy format. I figure that all I would have to do is use =COUNTIF (A1:A25, 11/*/09) and it would work. But that is not the case. when I add more calls/meetings/truck checks it does not add the new one to the total. And yes I did change A25 to A26 so that it new to look there also. But the total won't change. What am I doing wrong? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count days in excel
And if you really wanted to count the number of items in November of 2009:
=sumproduct(--(text(a1:a10,"yyyymm")="200911")) 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 ========= You could also count the dates larger than Oct 31, 2009 and subtract the dates larger or equal to Dec 1, 2009. =countif(a:a,""&date(2009,10,31)) - countif(a:a,"="&date(2009,12,1)) firemedic1203 wrote: I am trying to use countif to count the number of times within a month that my fire department has a call, meeting, truck check. I have the dates in mm/dd/yy format. I figure that all I would have to do is use =COUNTIF (A1:A25, 11/*/09) and it would work. But that is not the case. when I add more calls/meetings/truck checks it does not add the new one to the total. And yes I did change A25 to A26 so that it new to look there also. But the total won't change. What am I doing wrong? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003: Count total days elapsed | Excel Worksheet Functions | |||
Count Days more than 180 days prior to today | Excel Worksheet Functions | |||
When word says Install I want it count the days for 3 days w.o wee | Excel Worksheet Functions | |||
can I have excel count occurrences for 10 people in 7 days | Excel Worksheet Functions | |||
COUNT how many ROWS ago out of 10 days that the highest high in 10 days was made | Excel Worksheet Functions |