+ Reply to Thread
Results 1 to 4 of 4

trying to use multiple list criteria in sumif with comma, not working?

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    Dike, IOWA
    MS-Off Ver
    Excel 2010
    Posts
    19

    trying to use multiple list criteria in sumif with comma, not working?

    Been banging my head on the keyboard for last 2 hours trying to figure out how I used to sum multiple criteria within a single range when using {"text","text"}, but not working.
    This formula only gives me the 10-1result, and I need to add for columns up each for the day, with 16 different criteria??
    Also, I don't really want a 15 line formula for only adding 4 criteria up over 4 columns. Is there a way to sum all 4 periods. P1,P2,P3,P4, only adding the 10-1, 10-2,10-3 criteria? 10-1,10-2 etc cells are dropdown lists.
    =SUMIF(B3:B13,{"10-1","10-2"},C3:C13)

    Sorry, my small file wouldn't upload?

    name P-1 Hrs P-2 Hrs P-3 Hrs P-4 Hrs
    a 10-1 8.1 13-2 1.0 NA NA
    b 10-1 8.1 NA NA NA
    y 10-1 8.0 NA NA NA
    d 10-3 8.0 NA NA NA
    r 10-1 7.5 NA NA NA
    f 10-1 8.0 NA NA NA
    g 10-2 9.0 NA NA NA
    gh 10-1 10.0 NA NA NA
    i 10-1 11.0 NA NA NA
    j 10-2 14.0 NA NA NA
    k 10-1 13.0 NA NA NA
    Last edited by Prodschdler; 12-05-2013 at 06:03 AM.

  2. #2
    Forum Contributor amit.wilson's Avatar
    Join Date
    07-09-2013
    Location
    Gotham
    MS-Off Ver
    Excel for Mac 2011
    Posts
    283

    Re: trying to use multiple list criteria in sumif with comma, not working?

    Hello P - can't you go with something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Cheers
    <-- If you're happy & you know it...click the star.:-)

  3. #3
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: trying to use multiple list criteria in sumif with comma, not working?

    Hi
    Try this formula:
    =SUMIFS(C2:C12,B2:B12,"=10-Jan")+SUMIFS(C2:C12,B2:B12,"=10-Feb")
    Good luck.
    Tony

  4. #4
    Registered User
    Join Date
    03-05-2014
    Location
    Yorkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: trying to use multiple list criteria in sumif with comma, not working?

    Quote Originally Posted by Prodschdler View Post
    Been banging my head on the keyboard for last 2 hours
    I too have been banging my head on a brick wall about something similar to this. Here's what I found:

    My formulae have somehow changed from

    =SUMIF(Budget,$U22,Withdrawals) to =SUMIF(Budget|$U22|Withdrawals) where Budget and Withdrawals are named ranges. I did not make these changes. I have always used commas, not vertical bars. I guess this is from an automatic upgrade.

    If I copy and paste a formula, it works in a different location.
    If I type the formula by hand it does not work
    If I edit a working forumula like the one above, to (say) =SUMIF(Budget|$U2a|Withdrawals) (ie change one character), then I get an error message.

    The help pages give the correct syntax as SUMIF(range, criteria, [sum_range])
    The pop-up help dialog strip shows SUMIF(range|criteria|[sum_range]) - ie shows the vertical 'or' bar.

    Then I cracked it. You need to have a space on either side of the vertical bar.

    So =SUMIF(Budget|$U22|Withdrawals) will not be accepted.
    But =SUMIF(Budget | $U22 | Withdrawals) will be accepted.

    But if you come to edit the formula that you have just entered, the spaces will have been removed, and you have to put them back again for the edited command to work.

    I hope that this saves a few heads and brick walls from being demolished.
    Last edited by jfheath; 03-05-2014 at 03:31 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. Using a Comma separated list as criteria for sumif function
    By yoshik in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-25-2013, 12:47 AM
  2. Creating a comma seperated list based on a search criteria from a column
    By HUGH JORGAN in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2013, 05:20 PM
  3. [SOLVED] SUMIF/SUMIFS not working using Date fields as criteria
    By waynees in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2013, 09:24 AM
  4. Sum values based on multiple criteria (comma delimited) in a single cell
    By leem888 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 01-07-2011, 02:49 PM
  5. Non Working SUMIF (more than 1 criteria)
    By ltmaiyk in forum Excel General
    Replies: 3
    Last Post: 02-02-2010, 01:07 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