Sumproduct

[Blake 7]

Hi Guys -

How can I adjust the sumproduct formula below to account for col c. Col c represents the number of instances of col a2 and b2, a3 and b3 ect.

Should make sense when looking at the attachment.

Cell c43 for example means there were 2 instances of cancellations on 17/01/2009 of 14 days. Therefore increasing the figures by one for that 14 day period.

=SUMPRODUCT(--($A$2:$A$975<=D2),--(($A$2:$A$975+($B$2:$B$975-1))>=D2))


Hope this makes sense!

[zbor]

I don't really understand what you want... but maybe this?

Please Login or Register  to view this content.

[Blake 7]

Hi - Sorry to take so long to come back to you. I've been in a meeting most of the day... Grrrr

What I need to achieve here is this: (I'll explain it as best I can in words but the attachment better demonstrates what I need to achieve).

The total number of cancellations for each date in col D. Col D are unique and consecutive dates from the min and max dates in col A.

Col A values are cancellation dates
Col B values are number of days that were cancelled
Col C values are a count of instances of a and b.

See example tab of the attached "this page demonstrates what I need to do but obviously this is not a sensible way to approach the task". As you will see from the formula tab, currently the formula looks at col a abd b but does not accont for multiple instances as in col c

[NBVC]

What happens at E57 onwards, and at E60 where there is another 2 and onwards? What if there is a 3, 4, etc in C?

[Blake 7]

Hi N - How's it going? and the little N?

Thanks for your response and for looking. This is really annoying as what I am trying to achive is really simple! its just very difficult to explain!! so thanks for taking the time to try to understand.

I have just looked at E57 and realised that what i wrote F42:G56 isn't right as the 2 in C43 relates to two instances of 14 days starting from07/01/2010 and not 11/01/2010 as I showed.

The tab Example best describes what i need and I have updated it to show what would happen if there was a 3 in col c. I have drawn what it is I am trying to achive if there is more than 1 in c.

This formula =SUMPRODUCT(--($A$2:$A$975<=D2),--(($A$2:$A$975+($B$2:$B$975-1))>=D2)) does exactly what I want but does not account for multiple instances.

Once again, thanks for bearing with me! not sure if its more frustrating to you or me!

[NBVC]

Maybe it's another one of those days where I can't think straight, but I can't wrap my head around what you intended results are.

Can you re-post the sheet, add some 2's and 3's randomly amongst the first 20 or so cells in Column C and show the expected results in column E based on those entries...

I am not exactly sure how you are correlating the number of cancelled bookings with dates.

[Blake 7]

No N - its not you. Its a real pain to describe.!

i'm at home now and about to put my angels to bed! What i'll do at work tomorrow is start again and re-post as you suggested adding more expected results.

Once again - thanks for getting back to me.

Off to read Lightening McQueen for the 5th night in a row!!!

Cheers mate