Count the dates in a month
 

I want to count the dates in the month of May 2006 that are a "yes" condition
based on a column.

=SUMPRODUCT(--(Odyssey!$E$2:$E$605="Yes"),--(Odyssey!$AK$2:$AK$605="5/1/06
and 5/31/06"))

does not work

Thanks

--
Message posted via http://www.officekb.com

Count the dates in a month
 

=SUMPRODUCT(--(Odyssey!$E$2:$E$605="Yes"),
--(text(Odyssey!$AK$2:$AK$605,"yyyymm")="200605"))

is one way.


"s2m via OfficeKB.com" wrote:

I want to count the dates in the month of May 2006 that are a "yes" condition
based on a column.

=SUMPRODUCT(--(Odyssey!$E$2:$E$605="Yes"),--(Odyssey!$AK$2:$AK$605="5/1/06
and 5/31/06"))

does not work

Thanks

--
Message posted via http://www.officekb.com

--

Dave Peterson

Count the dates in a month
 

Assuming you want to count all dates in May 2006 if E = yes then.


=SUMPRODUCT((Odyssey!$E$2:$E$605="yes")*(Odyssey!$ AK$2:$AK$605=DATE(2006,5,1))*(Odyssey!$AK$2:$AK$6 05<=DATE(2006,5,31)))

Dates are recognized as numbers in Excel so if you were using
"5/1/2006", excel wouldn't recognize that because it is text.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=568365

Count the dates in a month
 

That works.

Is there a way I can create a variable for a defined date range? Something
like this APR = 4/1/06 thru 4/30/06.

This way I could just use one statement instead of having 2, something like
this

=SUMPRODUCT((Odyssey!$E$2:$E$700=$A4)*(Odyssey!$S$ 2:$S$700=APR))

Thanks



SteveG wrote:
Assuming you want to count all dates in May 2006 if E = yes then.

=SUMPRODUCT((Odyssey!$E$2:$E$605="yes")*(Odyssey! $AK$2:$AK$605=DATE(2006,5,1))*(Odyssey!$AK$2:$AK$ 605<=DATE(2006,5,31)))

Dates are recognized as numbers in Excel so if you were usin
"5/1/2006", excel wouldn't recognize that because it is text.

HTH

Stev

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

Count the dates in a month
 

If it's for a single year:

=SUMPRODUCT((Odyssey!$E$2:$E$700=$A4)*(Month(Odyss ey!$S$2:$S$700)=APR))

Set APR=4

OR

=SUMPRODUCT((Odyssey!$E$2:$E$700=$A4)*(Month(Odyss ey!$S$2:$S$700)=RepMonth))

Set RepMonth to be 1-12 as required

HTH

"s2m via OfficeKB.com" wrote:

That works.

Is there a way I can create a variable for a defined date range? Something
like this APR = 4/1/06 thru 4/30/06.

This way I could just use one statement instead of having 2, something like
this

=SUMPRODUCT((Odyssey!$E$2:$E$700=$A4)*(Odyssey!$S$ 2:$S$700=APR))

Thanks



SteveG wrote:
Assuming you want to count all dates in May 2006 if E = yes then.

=SUMPRODUCT((Odyssey!$E$2:$E$605="yes")*(Odyssey! $AK$2:$AK$605=DATE(2006,5,1))*(Odyssey!$AK$2:$AK$ 605<=DATE(2006,5,31)))

Dates are recognized as numbers in Excel so if you were usin
"5/1/2006", excel wouldn't recognize that because it is text.

HTH

Stev

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

Count the dates in a month
 

Yes,

You can create a list of all the dates in April and name that range APR
and apply it to your formula. Say A1:A30 = 4/1/2006 - 4/30/2006.
Select the range. Go to Insert,Name,Define. Type the name you want
and click OK.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=568365