+ Reply to Thread
Results 1 to 10 of 10

Dynamic Criteria for SUMIF, multiple selections

  1. #1
    Registered User
    Join Date
    05-31-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Dynamic Criteria for SUMIF, multiple selections

    Hi,

    I was hoping somebody could help me with a formula I have set up. Currently, the formula is a SUMIF formula which looks for criteria in a particular cell.

    =SUMIF(TABLE1,C1, TABLE1[Category])

    I may have written this out wrong but I hope you understand what I mean. Currently C1 is a list with different names, if I choose a name then the formula will give me the correct result for that. I was wondering whether it was possible to choose more than one item, like category may be A or B. I understand I can do this by adding a second criteria to the end of the formula but this type of formula will be repeated many times on the same sheet and I would prefer not having to change the structure by adding or removing a criteria. There will be cases where I will want to check only one category and others maybe quite a few.

    Any help is much appreciated.
    Many thanks

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Dynamic Criteria for SUMIF, multiple selections

    more than one item, like category may be A or B
    you could try indirect. you maybe adding another column or a cell to reference the indirect

    something like this one


    =SUMIF(INDIRECT("'"&$D$1&"'!$H$5:$H$200"),C1,INDIRECT("'"&$E$1&"'!$E$5:$E$200"))
    D1 houses the table
    E1 houses the category

    or you could try the sumifs
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    05-31-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic Criteria for SUMIF, multiple selections

    Quote Originally Posted by vlady View Post
    you could try indirect. you maybe adding another column or a cell to reference the indirect

    something like this one


    =SUMIF(INDIRECT("'"&$D$1&"'!$H$5:$H$200"),C1,INDIRECT("'"&$E$1&"'!$E$5:$E$200"))
    D1 houses the table
    E1 houses the category

    or you could try the sumifs
    Hi,

    The figures I am getting are correct. What I was hoping for in a formula was the ability to say the criteria may be Category A, B or C. Or just A. How could I do this dynamically using drop down lists so that if i were to select just A, the formula will look for all category A stuff. If I selected A and B, then it would automatically adjust the formula to search for values where Category is A or B. I am unsure if this is even possible, to make the formula adjustable in length and structure based on your choices from a drop down.

    I hope that makes sense.

    Thanks

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Dynamic Criteria for SUMIF, multiple selections

    can post a sample workbook so we can have the exact ranges and cell references thanks.

  5. #5
    Registered User
    Join Date
    05-31-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic Criteria for SUMIF, multiple selections

    How do I upload an excel file? Cannot find the option here.

  6. #6
    Registered User
    Join Date
    05-31-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic Criteria for SUMIF, multiple selections

    SUMIFS Sample.xlsx

    Never Mind

    So I have attached a sample file. Basically I want to be able to have one formula where I can sum all the amounts where category may be A. But then have the option to sum amounts where categories are A or B using lists and a formula somehow. I know how to do these as independant formulas but need it so that it is automatically summed where one, two or even more choices of category is made.

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Dynamic Criteria for SUMIF, multiple selections

    So you mean to say first you selected category A (sum all A) then you change your category to B so you need the Sum of "A" from previous sumif and "B total"

    Nope you can't do that in a single cell and with a formula. Maybe VBA...

    the only thing i can think is to create independent category with sumifs then get the total of those category

    SUMIFS Sample.xlsx

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,202

    Re: Dynamic Criteria for SUMIF, multiple selections

    Try the attached, I used a helper column - and added more categories in H1:C1. The formula I used in the helper makes provision for data to go out as far as J1, adjust this if needed.
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    05-31-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamic Criteria for SUMIF, multiple selections

    Thank you for the replies, I think I am having difficulty explaining my issue well. The sample file I have given is the simple version of my problem. In my actual file i am attempting to do the following.

    I have numerous projects, each project has a donor, it also has a payment schedule which is currently being recorded as payment 1 date, payment 1 amount. This payment schedule has many columns, a project may have up to 12 payments. The way this is being recorded is not very good, a change is being made to the table structure but I need to sum the amounts by year and/or month in the mean time.

    To do this I used a SUMIFS formula that summed the total of all projects cash payments 1 amount in a given year. I repeated this for all the cash payments and got an overall value for the given year. I then changed the formula to make the year selectable, so that I can change it on the fly. However, I want to be able to say sum all payments where year is 2012 and 2013 for example or 2011 and 2012.

    Equally I want to be able to ask to sum all payments by donor, I have already done it so one donor is selectable. I was hoping for it to be possible for me to find out the sum of all payments if donor was A, B or C. Rather than just having to choose one particular donor. Like I mentioned before, I am able to do this on a single formula each time I want a particular sum, but was hoping for this to be possible using the drop down lists as the file would be shared with somoeone who is not proficient with excel. I would like to avoid having to create separate sheets for choices with one category chosen and another for two and so on.

    Hope that makes sense. Other thing to note is that the table was imported and is refreshed for new data so cannot change the table in excel. The actual table is being changed and this issue won't be present once it is changed but needed this as an interim solution.

    Thanks again.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,202

    Re: Dynamic Criteria for SUMIF, multiple selections

    I just noticed that the file I uploaded contained the wrong formula in the helper column (D). Replace it with this 1...
    =IF(ISNUMBER(MATCH(A2,$F$1:$I$1,0)),C2,0)

    If the file you uploaded does not realistically match your actual file/data, I suggest you upload 1 that does - otherwise we are just going to go around in circles guessing at answers

+ 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. [SOLVED] Multiple criteria selections
    By petschek in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2012, 10:26 PM
  2. SUMIF with Dynamic Criteria
    By nofzinger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-11-2010, 06:17 PM
  3. SUMIF with dynamic criteria
    By inky in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-08-2008, 08:59 AM
  4. help with multiple criteria selections
    By rwethington in forum Excel General
    Replies: 2
    Last Post: 08-31-2006, 01:32 PM
  5. multiple selections in a sumif functions
    By Frank in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2005, 09:05 AM

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