+ Reply to Thread
Results 1 to 5 of 5

Sum if based on ranges of conditions

  1. #1
    Registered User
    Join Date
    03-26-2014
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    2

    Sum if based on ranges of conditions

    Hi all,

    I need to do a SUMIF that is rather advanced compared to what I'm used to since it must compare arrays of different sizes (or match them sequentially). I've attached a workbook example.

    I have an array A that consists of different types a, b, c, d, e, f.

    Then an array B that contains ranges (lower and upper bounds) of unique identifiers that are connected with either a, b, c, d, e, or f (i.e. 1000 to 1099 belongs to a, 1100 to 1249 belongs to a, and 3000 to 3099 belongs to a, and so forth for other types).

    Finally an array C that contains a very large data range of a given identifier and an associated value.

    I need to do a sumif of the values in array C, given that the identifier is within any of the given ranges in array B that are associated with a given type.

    Currently, my only solution is to split it up into several formulae parts, where I have 1 SUMIFS() for each identifier in array B that matches the type in array A. But this is unfeasible in my actual workbook and very manual.

    Is there a way to do this in one formula? I've tried SUM(IF()) arrays, but that won't work as it usually does, since the arrays are not of the same size.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Sum if based on ranges of conditions

    It would be easier to use a helper column and fill that with the appropriate code, e.g. put this formula in L4:

    =VLOOKUP(J4,$E$4:$G$14,3)

    and copy that down to the bottom of your data list. Then you can use this in B4:

    =SUMIF(L:L,A4,K:K)

    and copy that down.

    Hope this helps.

    Pete

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Sum if based on ranges of conditions

    Duplicated post.

    Pete

  4. #4
    Registered User
    Join Date
    03-26-2014
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Sum if based on ranges of conditions

    Hi Pete,

    Thanks for your suggestion. That would work well, given that I had the option to add that column. Unfortunately, in the data I have, I need to calculate it directly as I cannot modify the source data.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Sum if based on ranges of conditions

    Quote Originally Posted by thomasms90 View Post
    ... I need to calculate it directly as I cannot modify the source data ...
    Okay, well good luck with that - I've given you a solution which is simple and robust.

    Pete

+ 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. Concatenate cells or ranges based on conditions
    By andrewdaus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-01-2009, 06:45 PM
  2. Replies: 13
    Last Post: 07-08-2009, 04:27 PM
  3. [SOLVED] Add cells from a range based on 2 conditions from 2 other ranges
    By in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 07:05 AM
  4. Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  5. [SOLVED] Add cells from a range based on 2 conditions from 2 other ranges
    By Kelly in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. Add cells from a range based on 2 conditions from 2 other ranges
    By Kelly in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Add cells from a range based on 2 conditions from 2 other ranges
    By Kelly in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. [SOLVED] Add cells from a range based on 2 conditions from 2 other ranges
    By Kelly in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1