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

=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

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 using "5/1/2006", excel wouldn't recognize that because it is text.

HTH

Steve

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

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

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

"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
>
>