+ Reply to Thread
Results 1 to 22 of 22

sum a range of cells from empty to empty cell in a big table

  1. #1
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    sum a range of cells from empty to empty cell in a big table

    I have a table with multiple agents names, where the first raw contain the agent 1 name and then the month days, next is the next agent name with the month days, i want to sum the hours for each agent taking into consideration that the days will be diferent from month to month so the agents name will not be at the same cell each month
    Attached Files Attached Files
    Last edited by Ram71; 03-09-2018 at 12:31 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: sum a range of cells from empty to empty cell in a big table

    Hello and welcome to the forum.

    Please take a minute to include the desired results in your sample workbook. You can enter these manually.

    Also, your data will likely need to be reformatted into a tabular format but we will get into that after seeing your expected outcome.

  3. #3
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    sorry but how i can reattache the file

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: sum a range of cells from empty to empty cell in a big table

    You can click on "Edit Post" in the bottom right portion of post #1, then "Go Advanced", "Manage Attachments", and remove the attachment.

    Then attach the updated one as you did originally.

    Or just attach it to a new post.
    Last edited by 63falcondude; 03-09-2018 at 12:32 PM.

  5. #5
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    I have replace it
    thanks

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: sum a range of cells from empty to empty cell in a big table

    As mentioned in post #2, you want your data to be in tabular form, where there is one header row, no spaces, and one row per instance.

    See the 'PivotTable' worksheet in the attached workbook.

    This is how the data in Sheet1 should look. Once it is in this format, you can easily build a Pivot Table to show the totals by agent.

    To build the Pivot Table:
    1) Select/Highlight columns A:E
    2) Insert > PivotTable > Existing Worksheet > Location: G1 (you can put it wherever you want) > OK
    3) Drag "Agent" into ROWS and "total" into VALUES and make sure that it is taking the SUM of total by clicking on it > Value Field Settings > Sum > OK

    When you include more data into columns A:E, simply refresh the Pivot Table and the totals will be updated.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    My data is generated from an application and this is how it look like, I want to past the data each month to get the calculation

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: sum a range of cells from empty to empty cell in a big table

    Another option, that doesn't involve Pivot Tables, is to use the SUMIF function.

    With the data in tabular form as shown in the attachment from post #6, and with the agents in column G (starting in G2), put this in H2:

    =SUMIF(A:A,G2,E:E)

    Drag the formula down column H as far as needed.

  9. #9
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    someone gave me the bellow formula on a different table but at the same subject but when I have tried to apply it on my accual sheet it didn't work
    =if(C1=0,sum(C2:index(C1:C99,match(true,(C2:C99=""),0))),"")
    but the empty

  10. #10
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    the empty cell and the sum range where at the same colume

  11. #11
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: sum a range of cells from empty to empty cell in a big table

    Response to post #7:

    While I still recommend formatting your data in a way that is preferable for data analysis (tabular format), here is an option that allows you to keep the current formatting.

    E2 =SUM(D3:D9)
    E10 =SUM(D11:D17)
    E18 =SUM(D19:D25)

    when you want to add a new month of data in for Agent 1, copy the data, select row 9, then click on Insert. You will see that the formula in E2 now refers to D3:D10.

  12. #12
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    the number of days will not be the same and might be less or more for each agent each month
    and the number of days is not the same for each agent
    Last edited by Ram71; 03-09-2018 at 01:14 PM.

  13. #13
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: sum a range of cells from empty to empty cell in a big table

    The number of days per agent doesn't matter, I'm not sure why you think that it does.

    The idea behind post #11 is to extend the sum range into the row after the last row of data. Then by inserting the new data, the sum range will extend to include that new data.

    This will work even if Agent 1 has 10 rows of data and Agent 2 has 1000 rows of data.


    Since you seem hell bent on a single formula solution, even if (in my opinion) it isn't the best way of addressing the issue, try this modification to the formula from post #9 in E2:

    =IF(AND(A2<>"",B2=""),SUM(D2:INDEX(D3:D$99,MATCH(TRUE,(D3:D$99=""),0))),"") Ctrl Shift Enter

  14. #14
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    thanks but what is "Ctrl Shift Enter" does,

  15. #15
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: sum a range of cells from empty to empty cell in a big table

    Confirming a formula using Ctrl Shift Enter instead of just Enter is how to create an array formula.

    See here for more on array formulas.

  16. #16
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    can we go further on this formula

  17. #17
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    if i want to have the formula only sum the sundays and saturdays to calculate the bonuce

  18. #18
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: sum a range of cells from empty to empty cell in a big table

    This is exactly why it is important to have your data formatted in a way that Excel was meant to handle.

    While it might be possible to do what you are looking for with the data formatted how you want it, with a single complicated array formula, you are refusing to fix the problem at the source (the structure of the data) which would allow almost anything imaginable with relative ease.

    Maybe someone else will be willing to work with data in this structure and create an unnecessarily complicated formula that you approve of.

  19. #19
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    working on the data will cost me time
    I have more than 200 agent

  20. #20
    Registered User
    Join Date
    03-09-2018
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: sum a range of cells from empty to empty cell in a big table

    thank u anyway
    u were so helpful

  21. #21
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: sum a range of cells from empty to empty cell in a big table

    You're welcome. Thanks for the rep!

    Sometimes, it is best to spend some time up front to increase optimization and save time in the future.

    I'll bring this thread to others' attention.

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: sum a range of cells from empty to empty cell in a big table

    Based on 63falcondude's formula in post #13

    For Sat/Sun

    =IF(AND($A2<>"",$B2=""),SUMPRODUCT(($D3:INDEX($D3:$D$10000,MATCH(TRUE,($D3:$D$10000=""),0)))*(WEEKDAY($A3:INDEX($A3:$A$10000,MATCH(TRUE,($A3:$A$10000=""),0)),2)>5)),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files
    Last edited by JohnTopley; 03-09-2018 at 03:17 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to create a dynamic range if the last cell is empty and the columns have empty cells
    By peter_swe in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2016, 11:18 PM
  2. Replies: 0
    Last Post: 08-03-2016, 07:01 PM
  3. Replies: 2
    Last Post: 07-21-2016, 09:40 PM
  4. Replies: 1
    Last Post: 05-26-2014, 01:31 PM
  5. Need to find empty filled cells in a column and copy to the empty cell below
    By Grahamfeeley in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-30-2014, 10:09 AM
  6. MACRO to empty a range of cells based on whether specified cell is empty
    By TBJV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 10:35 PM
  7. Finding next empty empty cell in a range of columns
    By UncleBun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2006, 07:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1