SUM of numbers in a sequence

Copper Contributor

Hello. I have a billing report in Excel that I use to enter claims in a state system.  The system requires me to enter a record for each date, along with the number of hours and the total amount.  However, if the dates are in sequence, I have to combine them to create a single record entry.

RecipientIDService CodeService Date# HoursTotal HoursAmountTotal Amt
304423379W722412/1/20227.007.00$48.5848.58
304423379W722412/5/20227.00 $48.58 
304423379W722412/6/20225.00 $34.70 
304423379W722412/7/20227.00 $48.58 
304423379W722412/8/20225.0024.00$34.70$166.56
304423379W722412/12/20226.00 $41.64 
304423379W722412/13/20225.00 $34.70 
304423379W722412/14/20227.0018.00$48.58$124.92
304423379W722412/19/20227.00 $48.58 
304423379W722412/20/20227.00 $48.58 
304423379W722412/21/20227.00 $48.58 
304423379W722412/22/20227.0028.00$48.58$194.32

The data above would be entered as four (4) records, as follows:

Date FromDate ToHoursAmount
12/1/202212/1/20227.00$48.58
12/5/202212/8/202224.00$166.56
12/12/202212/14/202218.00$124.92
12/19/202212/22/202228.00$194.32

Is there any way for Excel to calculate the sequential dates data without my having to insert the Auto Sum for each sequence, manually?  My reports cover one month and can be upwards of 500 rows. Thank you.

4 Replies

@gdougher 

 

Take a look on a sample file.
All you need to do is input your "Date From" and "Date To"
and it will automatically calculate the hours and amounts within that date.

Hi! My Access dBase actually creates the records. I don’t input them. I copy and paste them to Excel. I just thought I could create a formula in Excel that would calculate the amounts that were sequential in date. No worries. Maybe I need to look at the whole process from another angle. Happy New Year!

@gdougher 

If you're on 365 with access to LAMBDA, there's an elegant solution available.

 

First, I created dynamic ranges for Service date, hours, and amount.

 

Created a named item for the Header:

 

 

={"Date From", "Date To", "Hours", "Amount"}

 

 

Obtained the Week Number:

 

 

=MAP(sdate, LAMBDA(e, WEEKNUM(e)))

 

 

 

Summarize LAMBDA:

 

 

 

=LAMBDA(a,v,LET(
    f, FILTER(HSTACK(sdate, hours, amount), WNum = v),
    VSTACK(a, HSTACK(MIN(TAKE(f, , 1)), MAX(f), SUM(TAKE(DROP(f, , 1), , 1)), SUM(TAKE(f, , -1))))
))

 

Sheet level formula:

 

 

=REDUCE(Header,UNIQUE(WNum),Summarize)

 

 

Hi @gdougher (Happy New Year too)

 

My Access dBase ... Maybe I need to look at the whole process from another angle

#1 Instead of copy/paste from Access to Excel, why not using Get & Tranform (from Excel) to get the data from Access

#2 Once the records are gathered, aggregate as desired with Power Query and return the output to an Excel sheet:

 

Sample.png

 

The attached sample doesn't get the data from Access but it's fairly easy to setup

Any question feel free to ask