+ Reply to Thread
Results 1 to 12 of 12

How to sum column arrays based on criteria in multiple rows...?

  1. #1
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    How to sum column arrays based on criteria in multiple rows...?

    Hi all,

    In Column F, I am manually suming the units of the hours worked (column c) for each individual employee (column a) each week end date (column b). So for example, this is tedious as there are 4000+ rows to go through. I want to automate summing all of the hours for week ending 1/3 for Susan Appleton, which totals 4.5 hours (cell F4). For Susan, there three rows included in this array. I also want to sum all of the hours for week ending 1/3 for Tom Arjangian, which totals 32 hours. For Tom, there are only two rows included in this array. Because the number of rows affected changes from person to person, it won't work to copy and paste down the formula. This is why I have to manually enter it...any ideas on how to automate this? See below for example:
    A B C D E F
    1 Person Week Ending Date Hours Non-Hours Time Off Hours SUM FORMULA
    2 Appleton, Susan 1/3/2016 4.5 0 0
    3 Appleton, Susan 1/3/2016 0 27.5 0
    4 Appleton, Susan 1/3/2016 0 0 8 4.5
    5 Arjangian, Tom 1/3/2016 32 0 0
    6 Arjangian, Tom 1/3/2016 0 0 8 32
    7 Babcock, Daniel 1/3/2016 32 0 0
    8 Babcock, Daniel 1/3/2016 0 0 8 32
    9 Appleton, Susan 1/10/2016 5 0 0
    10 Appleton, Susan 1/10/2016 4 0 0
    11 Appleton, Susan 1/10/2016 2 0 0
    12 Appleton, Susan 1/10/2016 0 8 0 11
    13 Arjangian, Tom 1/10/2016 40 0 8 40
    14 Babcock, Daniel 1/10/2016 2 0 8
    15 Babcock, Daniel 1/10/2016 4 0 0
    16 Babcock, Daniel 1/10/2016 3 5 0 9

    right now to get the above, I am manually entering in =sum(C2:C4) to get Susan Appleton's total hours in F4. This includes 3 rows (rows 2, 3, 4)... then in F6, I manually typed in =sum(C5:C6) which includes 2 rows (rows 5 & 6). Because the # of rows is constantly changing, i don't know how to automate this process.

    Any ideas? Help?

    Thank you in advance for all of your amazing help!

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: How to sum column arrays based on criteria in multiple rows...?

    Try in F3 then drag down:

    =IF(A3=A2,"",SUMIFS($C$2:C3,$A$2:A3,A3,$B$2:B3,B3))
    Quang PT

  3. #3
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: How to sum column arrays based on criteria in multiple rows...?

    Quote Originally Posted by bebo021999 View Post
    Try in F3 then drag down:

    =IF(A3=A2,"",SUMIFS($C$2:C3,$A$2:A3,A3,$B$2:B3,B3))
    Hi Bebo, Thanks for the quick reply! That doesn't quite work, but it's getting closer...see my attached workbook for what your formula does. When I put it in F3, it does not sum Susan Appleton's hrs...which should equal 4.5. instead it leaves it blank?

    SumQuestionforExcelForum.xlsx


    Do you see? Any advice?

    Thank you!
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: How to sum column arrays based on criteria in multiple rows...?

    Sorry just a slight correction

    =IF(A3=A4,"",SUMIFS($C$2:C3,$A$2:A3,A3,$B$2:B3,B3))

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: How to sum column arrays based on criteria in multiple rows...?

    Hi tvwright,

    This looks like a Pivot Table problem to me. There are lots of ways to display your data by simply dragging around the field names in the Pivot Table list boxes. See the attached to see if this is what you want. Also - No formulas needed when using Pivots like this. (the site isn't letting me attach my file right now. .. I'll try again in a few minutes.)
    Last edited by MarvinP; 02-16-2016 at 10:33 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: How to sum column arrays based on criteria in multiple rows...?

    Quote Originally Posted by bebo021999 View Post
    Sorry just a slight correction

    =IF(A3=A4,"",SUMIFS($C$2:C3,$A$2:A3,A3,$B$2:B3,B3))
    Hi Bebo....this is even closer...but still it doesn't quite work. See attached spreadsheet. It gets off track when it reaches "Maria Boncimino" (Cell F29) -- it shows zero when it should be 26.

    Do you know why this is? I think your formula only works for arrays that are 2 or 3 tows long, whereas my data may have arrays that may be 7+ rows long.

  7. #7
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: How to sum column arrays based on criteria in multiple rows...?

    Quote Originally Posted by MarvinP View Post
    Hi tvwright,

    This looks like a Pivot Table problem to me. There are lots of ways to display your data by simply dragging around the field names in the Pivot Table list boxes. See the attached to see if this is what you want. Also - No formulas needed when using Pivots like this. (the site isn't letting me attach my file right now. .. I'll try again in a few minutes.)
    hi martin, no, I do not want a Pivot table. I am massaging the data to be in Tabular format so I can import it into tableau. A pivot table is not the right format for Tableau -- I would have to do additional work in order to format it correctly with a Pivot table.

    Also -- you can attach your file by clicking the "attach image" button -- the attach file wasn't working for me either so I attached it via the attach image button!

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: How to sum column arrays based on criteria in multiple rows...?

    Hi tvwright,

    It's Marvin not Martin. I don't have an "attach image" button. I do believe a Pivot Table is a great way to do your problem. After you do the Pivot you can copy and paste using values only that might make Tableau like it. I'll try again later to attach my file. I think you would really like Pivots.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: How to sum column arrays based on criteria in multiple rows...?

    One more condition for Date criteria:

    =IF(AND(A3=A4,B3=B4),"",SUMIFS($C$2:C3,$A$2:A3,A3,$B$2:B3,B3))

  10. #10
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: How to sum column arrays based on criteria in multiple rows...?

    Quote Originally Posted by MarvinP View Post
    Hi tvwright,

    It's Marvin not Martin. I don't have an "attach image" button. I do believe a Pivot Table is a great way to do your problem. After you do the Pivot you can copy and paste using values only that might make Tableau like it. I'll try again later to attach my file. I think you would really like Pivots.
    Sorry Marvin! My friends' name is Martin so I had his name on my mind for some reason. OK i will wait out to see if BEBO has a responds ... the pivot table would require a lot of additional work for me to get the data to be formatted correctly for Tableau unfortunately.

  11. #11
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: How to sum column arrays based on criteria in multiple rows...?

    Quote Originally Posted by bebo021999 View Post
    One more condition for Date criteria:

    =IF(AND(A3=A4,B3=B4),"",SUMIFS($C$2:C3,$A$2:A3,A3,$B$2:B3,B3))
    I think this works for the most part -- still ran into an issue with F29 showing a "zero" when it should be a 26...but the 26 was calculated above it! So that should work. THANK YOU Rep points

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: How to sum column arrays based on criteria in multiple rows...?

    Nice to hear it works. You are welcome and thx for the feedback.

+ 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. copying multiple rows based on column criteria
    By harrydnyc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-27-2016, 06:08 PM
  2. Adding rows based on multiple column criteria
    By PitBoo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-16-2015, 06:53 PM
  3. [SOLVED] Copy rows from multiple worksheets based on criteria in column
    By Slavica in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-10-2014, 08:45 AM
  4. Dynamic Arrays - Selecting some rows based on criteria
    By AHFoddeR in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-23-2013, 03:55 AM
  5. Replies: 1
    Last Post: 01-30-2013, 12:12 PM
  6. Delete Multiple Rows Based on Criteria in 1 Column
    By Farmer12345 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-20-2013, 04:41 PM
  7. Replies: 6
    Last Post: 10-18-2012, 03:10 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