+ Reply to Thread
Results 1 to 7 of 7

sumifs with a criteria summing

  1. 01-13-2014, 09:44 PM #1
    watkec
    watkec is offline
    Registered User
    Join Date
    10-28-2007
    Posts
    21

    sumifs with a criteria summing

    I have a very large table with one column that has 32 different codes. I want to get the sum in other data columns for each of the codes. For example:

    Col B C D
    BAH01 1511 751
    BAH01 404 116
    BAH01 1281 706
    BAH01 536 211
    BAH01 3853 1700
    BAH01 1820 865
    BAH01 2813 1217
    BAH01 5317 2411
    BAH01 1397 763


    My sumifs statement for column C is this: =SUMIFS(C2:C72740,B2:B72740,BAH01)

    However, two things happen:

    1. Excel strips out the "0" in BAH01 and changes it to BAH1
    2. Even if I change the code (which I don't want to do) to BAH1 -- it still does not sum the values -- I just get "0" as the sum.

    What am I doing wrong?
    Reply With QuoteRegister To Reply
  2. 01-13-2014, 09:46 PM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: sumifs with a criteria summing

    Use "BAH01" instead of BAH01
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 01-13-2014, 09:49 PM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: sumifs with a criteria summing

    In Excel 2007 and later BAH1 is a valid cell address. I think Excel is confusing the text string BAH01 with the cell address BAH1.

    Try quoting it in the formula.

    =SUMIF(B2:B72740,"BAH01",C2:C72740)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 01-13-2014, 09:50 PM #4
    watkec
    watkec is offline
    Registered User
    Join Date
    10-28-2007
    Posts
    21

    Re: sumifs with a criteria summing

    wow - thanks! I don't do this everyday and I knew I was missing something. Perfect !!
    Reply With QuoteRegister To Reply
  5. 01-13-2014, 09:52 PM #5
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: sumifs with a criteria summing

    You're welcome and thank you for your feedback!
    Reply With QuoteRegister To Reply
  6. 01-13-2014, 09:56 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: sumifs with a criteria summing

    Or, use a cell to hold the criteria:

    A1 = BAH01

    =SUMIF(B2:B72740,A1,C2:C72740)

    Since there's just a single criteria you can just use the SUMIF function although SUMIFS will still work.
    Reply With QuoteRegister To Reply
  7. 01-13-2014, 10:23 PM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: sumifs with a criteria summing

    If your question has been answered please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools>Mark this thread as solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] sumifs function not summing between two dates
    By PhoenixFaery in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 04:57 AM
  2. SUMMING SUMIFS's
    By HangMan in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-17-2013, 11:52 AM
  3. SUMIFS with 1 criteria range, multiple criteria with cell references
    By heatherromo in forum Excel General
    Replies: 3
    Last Post: 11-21-2012, 04:57 PM
  4. How to ignore criteria set in SUMIFS fuction if criteria cell is blank?
    By Sam - excel rookie in forum Excel General
    Replies: 5
    Last Post: 09-07-2011, 11:14 PM
  5. Excel 2007 : Sumifs formula lookup multiple criteria in criteria range
    By matthews311 in forum Excel General
    Replies: 1
    Last Post: 05-16-2011, 05:00 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1