Results 1 to 8 of 8

Thread: Sumif On Multiple Columns Using Multiple Criteria By Passing A Range Of Values

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    3
    Rep Power
    0

    Sumif On Multiple Columns Using Multiple Criteria By Passing A Range Of Values

    I am looking for a formula (or macro) that can return the aggregate a range of numbers for a country, having taken account of multiple criteria for multiple columns.

    Please see .xlsx for example.


    This seems to be a simple problem but has even eluded professional modellers at work!

    Any help would be much appreciated!
    Attached Files Attached Files

  2. #2
    Member
    Join Date
    Jun 2012
    Posts
    39
    Rep Power
    0
    Sorry if I am not clear about the problem. Can you clearify one thing for me so that I can try helping you with your problem?

    The criteria columns seems exhaustive with the dummy set data. Can you please explain it for me?

    As far as I can think, sumproduct with '+' for multiple criteria in a criteria range is the solution for the problem.

    Br,

  3. #3
    Junior Member
    Join Date
    May 2013
    Posts
    3
    Rep Power
    0

    Wink

    Sure,

    Although the criteria is exhaustive at the moment, the idea is that I will be able to delete (or select) specific criteria and the formula would update to reflect the new totals.... eventually I intend to have these criteria as 'tick boxes' so that I can select or de-select at will and the (required) forula will update automatically.

    Having tried (and failed) with the sumproduct function (it returned incorrect amounts), im thinking that an advanced filter macro followed by some kind of sum function might be the answer, but would love it the sumproduct function does have the answer! .

    Thanks again
    Last edited by Excel Fox; 05-15-2013 at 02:55 PM. Reason: Quote Removed

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    You've included ALL possible criteria in the criteria column. Based on that, US should be 37, UK 33 and China 50. Is that correct?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    I am guessing this should suffice

    =SUMPRODUCT(ISNUMBER(MATCH($C$17:$C$43,$B$7:$B$10, 0)*MATCH($D$17:$D$43,$C$7:$C$10,0)*MATCH($E$17:$E$ 43,$D$7:$D$10,0))*($F$17:$F$43)*($B$17:$B$43=F7))
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Here's the file BTW. And yes, it's a SUMPRODUCT
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    May 2013
    Posts
    3
    Rep Power
    0
    That is EXACTLY what Im after, (and have been for a while!) Many Thanks! (I shall leave the modelling expert at work to stew over this a little longer as punishment!)

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Great. Glad you found what you were looking for. And the excel modelling expert, don't cane him . 'Expert' is a relative term.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  2. Sum values based on multiple criteria
    By Jorrg1 in forum Excel Help
    Replies: 8
    Last Post: 01-07-2013, 03:04 PM
  3. Replies: 4
    Last Post: 08-11-2012, 10:50 PM
  4. Sum values based on multiple criteria
    By vmath in forum Excel Help
    Replies: 1
    Last Post: 05-07-2012, 08:53 AM
  5. Fetch multiple values based on criteria
    By Lucero in forum Excel Help
    Replies: 8
    Last Post: 04-07-2012, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •