Count the dates in a month

[s2m via OfficeKB.com]

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

 

[Dave Peterson]

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

is one way.

 

[SteveG]

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

 

[s2m via OfficeKB.com]

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

 

[Toppers]

If it's for a single year:

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

Set APR=4

OR

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

Set RepMonth to be 1-12 as required

HTH

 

[SteveG]

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