Adding numbers based on consecutive dates

Question

Hi,

I'm trying to add the 'Sum of Days' column for consecutive dates for the same 'Code'. This is proving to be a little challenging.

I would really appreciate if someone could help me out.

Thanks.

Answer 1

Good Day, Hi, This is Jomar,

Thank you for reaching out to us. I am an Independent Advisor and Microsoft user like you.

This link would be really helpful for you:

https://support.microsoft.com/en-us/office/crea...

Hope it helps a lot :)

Answer 2

Hi Thanks for sending through the video. Unfortunately this does not help as I will need to add the 'sum of days' only when the 'Codes' are the same and they happen on consecutive days.

Hope that clarifies.

Thanks

Answer 3

Hello Shaun! I'm Jen, an independent advisor and a Microsoft user like you. I'd be happy to help you out with this issue.

If I understand correctly, you want to add the days of specific 'Codes' that happen on consecutive dates. If this is the case, you can use the array formulas from the attached image below given cell E2 is where you enter the code while cell F2 gives the result or the sum of consecutive days for that given code.

Also, array formulas are not entered in the same way as 'standard' formulas. instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Hope this helps!

Regards,

Jen :)

Answer 4

Kindly see attached image. Thanks!

Answer 5

Hi Jen_L,

You are a legend! Thanks a lot. 

Just one more favour, can we filter this data by 'Year' and 'Code;? I also need the data to sum values only if their total of the sequential dates is greater than or equal to 3.

Many Thanks, once again.

Regards,

Shaun