software

Hi. I have this equation:

=(IF(($Q10gt;=$C$10)*($Q10lt;=$D$10),J$10,0))
(IF(($Q10gt;=$C$11)*($Q10lt;=$D$11),J$11,0)) if if if... and so on...

Which means:
if (date gt;= startdate and date lt;= endate) then show the value for that day,
or 0 if date doesn't fall inbetween the start and end date...

Which is working but I need to do this for every day of the month so: a/
Excel won't let me write an equation long enough to have 31 of these and b/
it's a real pain having to write it out for every month!

Does anyone know a better way?! Thanks!
Hi
Since your talking about 31 days, then I guess the start date is the
beginning of a month and the end date is the last date of the month.
If so, perhaps you could use something like
=IF(MONTH(Q10=MONTH(C10),J10,0)
or, if you have the Analysis Toolpack loaded (Toolsgt;Addinsgt;Analysis
Toolpack) then may
=IF(Q10lt;=EOMONTH(C1,0),J10,0)

Insert your absolute references to suit.--
Regards

Roger Govierquot;Statlerquot; gt; wrote in message
...
gt; Hi. I have this equation:
gt;
gt; =(IF(($Q10gt;=$C$10)*($Q10lt;=$D$10),J$10,0))
gt; (IF(($Q10gt;=$C$11)*($Q10lt;=$D$11),J$11,0)) if if if... and so on...
gt;
gt; Which means:
gt; if (date gt;= startdate and date lt;= endate) then show the value for that
gt; day,
gt; or 0 if date doesn't fall inbetween the start and end date...
gt;
gt; Which is working but I need to do this for every day of the month so:
gt; a/
gt; Excel won't let me write an equation long enough to have 31 of these
gt; and b/
gt; it's a real pain having to write it out for every month!
gt;
gt; Does anyone know a better way?! Thanks!
gt;
gt;
Use

=SUMPRODUCT(--(C10:C24lt;=Q10),--(D1024gt;=Q10),J10:J24)

change the 24 to your last cell

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Statlerquot; gt; wrote in message
...
gt; Hi. I have this equation:
gt;
gt; =(IF(($Q10gt;=$C$10)*($Q10lt;=$D$10),J$10,0))
gt; (IF(($Q10gt;=$C$11)*($Q10lt;=$D$11),J$11,0)) if if if... and so on...
gt;
gt; Which means:
gt; if (date gt;= startdate and date lt;= endate) then show the value for that
day,
gt; or 0 if date doesn't fall inbetween the start and end date...
gt;
gt; Which is working but I need to do this for every day of the month so: a/
gt; Excel won't let me write an equation long enough to have 31 of these and
b/
gt; it's a real pain having to write it out for every month!
gt;
gt; Does anyone know a better way?! Thanks!
gt;
gt;