+ Reply to Thread
Results 1 to 9 of 9

Sum of cells based on criteria

  1. #1
    Registered User
    Join Date
    09-29-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    33

    Sum of cells based on criteria

    I am trying to find a formula that will sum cells based on criteria. For example, if I select an input of 1, I need it to add cells A1 and B6,11,16, & 21. An input of 2 needs to sum cells A1, A5 and B11, 16, & 21. I was wondering if INDEX would work in this case. Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Sum of cells based on criteria

    Your expected answer at K10 is incorrect:

    =SUM(OFFSET($K$2,,,,J6))+IFERROR(SUM(OFFSET($O$2,1,,,-(COUNTA($K$2:$O$2)-J6))),0)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Sum of cells based on criteria

    Hi, to both!

    Agree with @Glenn Kennedy. I Leave another option, without volatile functions:
    [K6] : =SUM(K$2:INDEX(K$2:O$2,J6),INDEX(K$3:P$3,1+J6):P$3)

    And drag it down. Blessings!

  4. #4
    Registered User
    Join Date
    09-29-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    33

    Re: Sum of cells based on criteria

    Thanks, but I need the specific cell references, would it be possible to include those? I should have deleted the example with numbers on the right hand side, I see now how it can be confusing.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Sum of cells based on criteria

    Post a sheet showing what you ACTUALLY want.

  6. #6
    Registered User
    Join Date
    09-29-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    33

    Re: Sum of cells based on criteria

    Sorry guys, I don't think I did a good job with explaining my initial post. Would someone be able to take a look at this new attachment? I really appreciate it.
    Attached Files Attached Files
    Last edited by thisismyname2; 10-18-2018 at 01:36 PM. Reason: Updated Excel file to make it more clear

  7. #7
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Sum of cells based on criteria

    Hi again!

    With you new file, this could be an option:
    =SUMPRODUCT(K$2:T$2*(COLUMN(K$2:T$2)-COLUMN(J$2)<2*J6)+K$3:T$3*(COLUMN(K$3:T$3)-COLUMN(J$3)>2*J6),MOD(COLUMN(K$2:T$2)-COLUMN(J$2),2))

    Blessings!

  8. #8
    Registered User
    Join Date
    05-09-2017
    Location
    Edmonton, Alberta
    MS-Off Ver
    2016
    Posts
    61

    Re: Sum of cells based on criteria

    I have a similar question.
    I have included an attachment.
    On the Inventory tab I have some inventory items, they are separated by Item Type, Item, Long/Short Sleeve, Size and who they are Assigned To.
    On the Summary tab I would like the B3 & B4 to analyze the data in the Inventory tab and count based on that criteria.

    For example:
    I would like cell B3 to look in the Inventory tab for: Shirt in Column B, and for Dress Shirt in Column C, and for Long Sleeve in Column D, and for M, in Column E.
    When it finds a combination of all of those then I would like it to count how many instances that occurs.
    In the example, it should find 2.

    Thank you for your help.
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,383

    Re: Sum of cells based on criteria

    Sorry, but asking your own question in someone else’s thread is not allowed - see rule #4. Therefore, please start your own thread. Thank you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. Combine text from multiple cells based on criteria from other cells
    By t2cool in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-07-2017, 12:03 PM
  2. [SOLVED] Lookup in a range of cells and extracting cells based on values and criteria
    By A_W in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-03-2016, 05:34 AM
  3. [SOLVED] Adding specific text to cells based on another cells data criteria
    By JohnnyBoyxxx in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-26-2015, 02:08 PM
  4. Replies: 4
    Last Post: 06-13-2014, 07:08 PM
  5. [SOLVED] Formula Needed to count a range of cells based on criteria in 2 other cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-01-2013, 04:06 PM
  6. Replies: 2
    Last Post: 06-24-2010, 04:53 PM
  7. Fill cells with color based on criteria in two cells
    By AA in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-02-2006, 07:35 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