+ Reply to Thread
Results 1 to 6 of 6

Sum Multiple Lines based on Multiple Criteria

  1. #1
    Registered User
    Join Date
    10-26-2018
    Location
    Sheffield, England
    MS-Off Ver
    Windows 7
    Posts
    3

    Sum Multiple Lines based on Multiple Criteria

    Hi,

    I'm sure the solution to this is pretty simple, but I can't work out whether the issue is because the sum is time or the formula's I am trying. I want the table to the right to sum the times that fit the criteria on both axis, so for example all the times for "Andrew W" in the state "Alerting" to sum and show in the table to the right (sorry but it won't let me attach a file for some reason).

    Thanks

    Capture.JPG

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,558

    Re: Sum Multiple Lines based on Multiple Criteria

    I'm guessing since there is no sample but maybe =SUMPRODUCT(($A:$A=$E2)*($B:$B=F$1)*($C:$C)) dragged down and right.

    AND, by the way, I'd adjust the ranges instead of using whole columns.
    Last edited by Sam Capricci; 10-29-2018 at 05:48 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,954

    Re: Sum Multiple Lines based on Multiple Criteria

    Please try

    F2 copy to H3
    =SUMIFS($C1:$C99,$A1:$A99,$E2,$B$1:$B$99,F$1)

    Select F2:H3 and set Custom Format to [hh]:mm:ss

  4. #4
    Registered User
    Join Date
    10-26-2018
    Location
    Sheffield, England
    MS-Off Ver
    Windows 7
    Posts
    3

    Re: Sum Multiple Lines based on Multiple Criteria

    Quote Originally Posted by Sambo kid View Post
    I'm guessing since there is no sample but maybe =SUMPRODUCT((A:A=$E2)*(B:B=F$1)*(C:C)) dragged down and right.
    Thanks for this, I thought it should be something like that too, but I just get #VALUE! error's.

  5. #5
    Registered User
    Join Date
    10-26-2018
    Location
    Sheffield, England
    MS-Off Ver
    Windows 7
    Posts
    3

    Re: Sum Multiple Lines based on Multiple Criteria

    Quote Originally Posted by Bo_Ry View Post
    Please try

    F2 copy to H3
    =SUMIFS($C1:$C99,$A1:$A99,$E2,$B$1:$B$99,F$1)

    Select F2:H3 and set Custom Format to [hh]:mm:ss
    That worked! Many Thanks

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,558

    Re: Sum Multiple Lines based on Multiple Criteria

    I forgot to add absolutes to mine so it likely would error out after the first column (see it now). But without a sample to test against I was only guessing. A sumifs is probably as good as option as any and so glad you got your answer from Bo_Ry.

+ 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] Merge multiple sheets data into 1 sheet based on multiple criteria (List Based)
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 43
    Last Post: 08-10-2018, 11:08 AM
  2. [SOLVED] Grab lines of data from sheet based on multiple criteria (working slightly)
    By kai. in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-30-2018, 04:43 AM
  3. Count/Find X on multiple lines w/ multiple criteria
    By artistapart in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-28-2016, 12:55 PM
  4. Match lines with Multiple Specific Criteria
    By stockgoblin42 in forum Excel General
    Replies: 0
    Last Post: 06-15-2016, 11:23 PM
  5. Select a cell at random based on multiple selection criteria across multiple sheets.
    By scottyms in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2016, 09:44 AM
  6. [SOLVED] Find matching lines based on multiple criteria
    By Jonathan68 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2014, 06:04 PM
  7. Create multiple lines based upon criteria
    By SystemsAccountant in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-05-2008, 01:14 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