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

[Guest]

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!

 

[Roger Govier]

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

Insert your absolute references to suit.

 

[Bob Phillips]

Use

=SUMPRODUCT(--(C10:C24<=Q10),--(D1024>=Q10),J10:J24)

change the 24 to your last cell

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)