Need help with a formula for calculating based on a rage of dates

**djeans** · 07-02-2005, 11:23 AM

I am trying to add a formula to a schudule spread sheet that will allow me to input dates of different events happening in town, and how many people are expected to be at each event, then for each day give me a total number of potential people in town.

For example:
Cells B4:B11 = days of the week (7/4 - 7/10)

Cells B12 and B13 = Start date and end date of event (conventions etc.)

Cell B14 = Number of people at event.

I would like to have Cell A4:A11 contain the number of people in town.

If I only have one event in town, this is easy, but where I am running into a problem is when there is more than one convention in town, and with different begin and end dates.

For example:

B12 = 7/4, B13 = 7/10, B14 = 1000
C12 = 7/4, C13 = 7/5, C14 = 1000

I would like for Cell A4 and A5 to both show 2000 (total number of people in town for event) but A6 thru A11 to show 1000 ( first convention ended, second still going)

Can anybody give me a hint as to how to make this work?

Thanks in advance.

Darron

**duane** · 07-02-2005, 11:51 AM

in cell a4 and copied down thru a11

=SUMPRODUCT((B4>=$B$12:$C$12)*(B4<=$B$13:$C$13)*($B$14:$C$14))

**djeans** · 07-02-2005, 12:08 PM

Thank you for your reply,

I think that I made a stupid mistake in listing the formulas

Correction =

Cells B4:H4 = days of the week (7/4 - 7/10)

Cell I4 = Number of people at event.

Cells J4 and K4 = Start date and end date of event (conventions etc.)

I would like to have Cell B5:H5 contain the number of people in town.

Sorry for making such a rookie mistake.

Can you still help?

Thanks

Darron

**duane** · 07-02-2005, 12:12 PM

now i am confused because this last description only leaves room for one event (in row 4)

**djeans** · 07-02-2005, 12:37 PM

Sorry,

I should have specified, The events will basically follow in the same columns going down. IE, the next event would be in row 5, the third row 6, and so on.

There may be as few as 2 events, but as many as 10.

Thanks again for your patience and help.

Darron

**duane** · 07-02-2005, 12:43 PM

in cell b5 and copied thru h5

=SUMPRODUCT((B$4>=$J$4:$J$15)*(B$4<=$K$4:$K$15)*($I$4:$I$15))

**djeans** · 07-02-2005, 01:16 PM

We're getting closer!!

Not sure if I typed something in wrong, but each cell is returning 0

Clicked on trace empty cell, and red arrows were drawn from the Start date, end date, and number of ppl cells to the cell with the formula. Does that help?

Darron