Forum Discussion

gdougher's avatar
gdougher
Copper Contributor
Dec 29, 2022

SUM of numbers in a sequence

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.

excel

4 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jan 03, 2023

    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)

     

     

    • gdougher's avatar
      gdougher
      Copper Contributor
      Jan 03, 2023
      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!
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jan 03, 2023

        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 https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a (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:

         

         

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

        Any question feel free to ask

Resources