If+if+if+if... Does anyone know a better way?
 

Hi. I have this equation:

=(IF(($Q10=$C$10)*($Q10<=$D$10),J$10,0))
+(IF(($Q10=$C$11)*($Q10<=$D$11),J$11,0)) +if+if+if... and so on...

Which means:
if (date = startdate and date <= 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!

If+if+if+if... Does anyone know a better way?
 

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 (ToolsAddinsAnalysis
Toolpack) then may
=IF(Q10<=EOMONTH(C1,0),J10,0)

Insert your absolute references to suit.


--
Regards

Roger Govier


"Statler" wrote in message
...
Hi. I have this equation:

=(IF(($Q10=$C$10)*($Q10<=$D$10),J$10,0))
+(IF(($Q10=$C$11)*($Q10<=$D$11),J$11,0)) +if+if+if... and so on...

Which means:
if (date = startdate and date <= 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!

If+if+if+if... Does anyone know a better way?
 

Use

=SUMPRODUCT(--(C10:C24<=Q10),--(D10:D24=Q10),J10:J24)

change the 24 to your last cell

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Statler" wrote in message
...
Hi. I have this equation:

=(IF(($Q10=$C$10)*($Q10<=$D$10),J$10,0))
+(IF(($Q10=$C$11)*($Q10<=$D$11),J$11,0)) +if+if+if... and so on...

Which means:
if (date = startdate and date <= 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!