Hi. I have this formula:
=(IF(($Q10=$C$10)*($Q10<=$D$10),J$10,0))
+(IF(($Q10=$C$11)*($Q10<=$D$11),J$11,0))...and so on

which means:
if (startdate = date or enddate<= date) show value in J and if not, show 0

....and I need it to do this for every day in a month. However, a/ Excel
won't let me nest 31 if functions like this and b/ it's a real pain typing it
out for every month!

Does anyone know a better way? Thanks!

how about
=SUMPRODUCT(($C$10:$C$40=startdate)*($D$10:$D$40< =enddate)*($J$10:$J$40))
Or reverse the start and end date if I just switched them on you

"Statler" wrote in message
...
Hi. I have this formula:
=(IF(($Q10=$C$10)*($Q10<=$D$10),J$10,0))
+(IF(($Q10=$C$11)*($Q10<=$D$11),J$11,0))...and so on

which means:
if (startdate = date or enddate<= date) show value in J and if not, show
0

...and I need it to do this for every day in a month. However, a/ Excel
won't let me nest 31 if functions like this and b/ it's a real pain typing
it
out for every month!

Does anyone know a better way? Thanks!

Thanks for responding! I can't get that to work though...
I'll add to my explaination which may help:
The sales team can put in the start and end date of a campaign in two
columns (e.g. 1st Feb in A and 5th Feb in B) and the number of items sold in
C (e.g. 100). I need a formula (currentlymy set of if's!) that says, 'Check
todays date to see if it falls inbetween ANY of the date sets that the sales
team have put in (they need to put in lots of different sets on different
timescales) and if it does, show the number they have to sell that day. Using
the figures above, on the 5th Feb, the formula should return 20 (100 items, 5
days = 20/day). then if another sales input was 5th Feb- 9th Feb, 100 items,
the formula should show 20 for the 1, 2,3,4 of Feb, then 40 for the 5th (two
sales sets on that day both of 20) then 20 again for the 6, 7,8,9th and zero
for the 10th onwards...

Whew! If that makes sense and you can offer anything further then I'll be
hugely grateful! (And very impressed!)

Thanks!

=SUMPRODUCT(($C$10:$C$40=startdate)*($D$10:$D$40< =enddate)*($J$10:$J$40))
Or reverse the start and end date if I just switched them on you

Try this
=SUMPRODUCT(($A$10:$A$40<=TODAY())*($B$10:$B$40=T ODAY())*($C$10:$C$40/($B$10:$B$40-$A$10:$A$40+1)))

You can exchange the TODAY() calls with a pointer to date.

"Statler" wrote in message
...
Thanks for responding! I can't get that to work though...
I'll add to my explaination which may help:
The sales team can put in the start and end date of a campaign in two
columns (e.g. 1st Feb in A and 5th Feb in B) and the number of items sold
in
C (e.g. 100). I need a formula (currentlymy set of if's!) that says,
'Check
todays date to see if it falls inbetween ANY of the date sets that the
sales
team have put in (they need to put in lots of different sets on different
timescales) and if it does, show the number they have to sell that day.
Using
the figures above, on the 5th Feb, the formula should return 20 (100
items, 5
days = 20/day). then if another sales input was 5th Feb- 9th Feb, 100
items,
the formula should show 20 for the 1, 2,3,4 of Feb, then 40 for the 5th
(two
sales sets on that day both of 20) then 20 again for the 6, 7,8,9th and
zero
for the 10th onwards...

Whew! If that makes sense and you can offer anything further then I'll be
hugely grateful! (And very impressed!)

Thanks!

=SUMPRODUCT(($C$10:$C$40=startdate)*($D$10:$D$40< =enddate)*($J$10:$J$40))
Or reverse the start and end date if I just switched them on you

My previous will include fractions, this one gets rid of them
=SUMPRODUCT(($A$10:$A$40<=TODAY())*($B$10:$B$40=T ODAY())*(INT($C$10:$C$40/($B$10:$B$40-$A$10:$A$40+1))))+SUMPRODUCT(($A$10:$A$40<=TODAY() )*($B$10:$B$40=TODAY())*(MOD($C$10:$C$40,($B$10:$ B$40-$A$10:$A$40+1))=(TODAY()-$A$10:$A$40+1)))
If you have an even product, say you need to seel 15 in 6 days =2.5 per day,
this will give you 3 for each of the first 3 days and 2 for the last 3 days.
Should work, test it.


"Statler" wrote in message
...
Thanks for responding! I can't get that to work though...
I'll add to my explaination which may help:
The sales team can put in the start and end date of a campaign in two
columns (e.g. 1st Feb in A and 5th Feb in B) and the number of items sold
in
C (e.g. 100). I need a formula (currentlymy set of if's!) that says,
'Check
todays date to see if it falls inbetween ANY of the date sets that the
sales
team have put in (they need to put in lots of different sets on different
timescales) and if it does, show the number they have to sell that day.
Using
the figures above, on the 5th Feb, the formula should return 20 (100
items, 5
days = 20/day). then if another sales input was 5th Feb- 9th Feb, 100
items,
the formula should show 20 for the 1, 2,3,4 of Feb, then 40 for the 5th
(two
sales sets on that day both of 20) then 20 again for the 6, 7,8,9th and
zero
for the 10th onwards...

Whew! If that makes sense and you can offer anything further then I'll be
hugely grateful! (And very impressed!)

Thanks!

=SUMPRODUCT(($C$10:$C$40=startdate)*($D$10:$D$40< =enddate)*($J$10:$J$40))
Or reverse the start and end date if I just switched them on you

I can shorten that a bit
=SUMPRODUCT(($A$10:$A$40<=TODAY())*($B$10:$B$40=T ODAY())*(INT($C$10:$C$40/($B$10:$B$40-$A$10:$A$40+1))+(MOD($C$10:$C$40,($B$10:$B$40-$A$10:$A$40+1))=(TODAY()-$A$10:$A$40+1))))
Again, you can point the TODAY() calls at the date, copy down next to the
days of the month to make a forecast for the month.

"Bob Tarburton" wrote in message
...
My previous will include fractions, this one gets rid of them
=SUMPRODUCT(($A$10:$A$40<=TODAY())*($B$10:$B$40=T ODAY())*(INT($C$10:$C$40/($B$10:$B$40-$A$10:$A$40+1))))+SUMPRODUCT(($A$10:$A$40<=TODAY() )*($B$10:$B$40=TODAY())*(MOD($C$10:$C$40,($B$10:$ B$40-$A$10:$A$40+1))=(TODAY()-$A$10:$A$40+1)))
If you have an even product, say you need to seel 15 in 6 days =2.5 per
day, this will give you 3 for each of the first 3 days and 2 for the last
3 days.
Should work, test it.


"Statler" wrote in message
...
Thanks for responding! I can't get that to work though...
I'll add to my explaination which may help:
The sales team can put in the start and end date of a campaign in two
columns (e.g. 1st Feb in A and 5th Feb in B) and the number of items sold
in
C (e.g. 100). I need a formula (currentlymy set of if's!) that says,
'Check
todays date to see if it falls inbetween ANY of the date sets that the
sales
team have put in (they need to put in lots of different sets on different
timescales) and if it does, show the number they have to sell that day.
Using
the figures above, on the 5th Feb, the formula should return 20 (100
items, 5
days = 20/day). then if another sales input was 5th Feb- 9th Feb, 100
items,
the formula should show 20 for the 1, 2,3,4 of Feb, then 40 for the 5th
(two
sales sets on that day both of 20) then 20 again for the 6, 7,8,9th and
zero
for the 10th onwards...

Whew! If that makes sense and you can offer anything further then I'll be
hugely grateful! (And very impressed!)

Thanks!

=SUMPRODUCT(($C$10:$C$40=startdate)*($D$10:$D$40< =enddate)*($J$10:$J$40))
Or reverse the start and end date if I just switched them on you

Yes! Yes! Yes!

That works! Brilliant Bob, thanks soooo much! I don't quite understand it
yet but I can now go away and find out what the INT and MOD functions do (and
maybe swot up on the SUMPRODUCT too!) and then work back through so I can
grasp exactly what is going on there - but it's great to be able to do it
that way around rather than give up in frustration trying to work from the
start upwards!

Thanks again so much for your time.

Sumproduct is explained at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

INT(x) is essentially the same as ROUNDDOWN(x,0)
MOD(x,y) gives the remainder of x/y.

Glad to help and to finally get it right.

"Statler" wrote in message
...
Yes! Yes! Yes!

That works! Brilliant Bob, thanks soooo much! I don't quite understand it
yet but I can now go away and find out what the INT and MOD functions do
(and
maybe swot up on the SUMPRODUCT too!) and then work back through so I can
grasp exactly what is going on there - but it's great to be able to do it
that way around rather than give up in frustration trying to work from the
start upwards!

Thanks again so much for your time.