# Dynamic Criteria for SUMIF, multiple selections

1. ## 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  Register To Reply

2. ## 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  Register To Reply

3. ## Re: Dynamic Criteria for SUMIF, multiple selections Originally Posted by vlady 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  Register To Reply

4. ## Re: Dynamic Criteria for SUMIF, multiple selections

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

5. ## Re: Dynamic Criteria for SUMIF, multiple selections

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

6. ## 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.  Register To Reply

7. ## 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  Register To Reply

8. ## 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.  Register To Reply

9. ## 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.  Register To Reply

10. ## 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  Register To Reply