Countif function - using a date range

[Exceluser1]

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?

 

[Pete_UK]

Try this:

=SUMPRODUCT((MONTH(M2:M999)=9)*(YEAR(M2:M999)=2008))

Hope this helps.

Pete

 

[Dave]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

One more:
=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[Teethless mama]

=SUMPRODUCT(--(TEXT(M2:M999,"mmm-yy")="Sep-08"))

 

[Dave]

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.

 

[Sandy Mann]

"

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave]

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.

 

[Exceluser1]

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