+ Reply to Thread
Results 1 to 5 of 5

Summing values based on criteria (SUMIF issues)

  1. #1
    Registered User
    Join Date
    05-15-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    8

    Summing values based on criteria (SUMIF issues)

    Hello,

    An internal program exports in a way that I'm finding is difficult to work with. I have limited Excel experience but it seems like using SUMIF isn't working.

    Basically, I have a timeline that shows a count of how many people were out based on their code reason. For example, the timeline shows that at 13:00 1 person was a lunch and 3 people were on break - I need to know that during that time 4 people were out.

    Excel Help1.xlsx

    Seems simple but I'm struggling..

    What I was trying is: =SUMIF($A$4:$A$8,OR("LUNCH","BREAK"),N$4:N$8)
    but it's not summing anything.. just getting 0s.

    I have to do this for 45 columns into 10 groups so there's gotta be an easier way.
    Wouldn't mind using a macro but I didn't want to start before getting any formula to work.


    Thank you for any help/suggestions/pointers!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Summing values based on criteria (SUMIF issues)

    You cannot use OR() like that in sumif(), try this instead...
    =SUMIF($B$5:$B$8,"*paid*",C$5:C$8)

    Or maybe this...
    =SUM(SUMIF($A$5:$A$8,{"lunch","Break"},C5:C8))
    Last edited by FDibbins; 08-11-2014 at 08:21 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Re: Summing values based on criteria (SUMIF issues)

    notsamsnead, Good evening.

    Try to use it:

    =SUM(SUMIFS(C4:C8,$A$4:$A$8,{"Lunch","Break"}))

    Is it what you want?
    I hope it helps.
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marc?lio Lob?o

  4. #4
    Registered User
    Join Date
    05-15-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Summing values based on criteria (SUMIF issues)

    Yes, that works great!

    Can you explain why I needed to do it this way? Just so I can understand the solution better..

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Summing values based on criteria (SUMIF issues)

    SUMIF() on its own cannot take a range of criteria but it can use wild cards, so I looked at what else could be used as the basis for the selection - and saw they were both a type of "paid", which ledt to this...
    =SUMIF($B$5:$B$8,"*paid*",C$5:C$8)

    In order to use a range of criteria in sumif(), you need to create an array, and then sum the contents of the ARRAY that meet the criteria, which then results in mt 2nd suggestion...
    =SUM(SUMIF($A$5:$A$8,{"lunch","Break"},C5:C8))

    Hope that helps?

+ 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. Summing Values in column based upon multiple criteria
    By shantanuk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-21-2012, 09:29 AM
  2. Replies: 4
    Last Post: 12-29-2010, 12:44 AM
  3. Summing based on TWO search criteria (SUMIF?)
    By JBeaucaire in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2008, 01:28 PM
  4. Summing values based on two criteria
    By smartguy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-16-2007, 04:46 PM
  5. Summing Values Based on Text Criteria
    By ExcelTip in forum Tips and Tutorials
    Replies: 4
    Last Post: 11-02-2005, 10:58 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