SUMIFS Formula

NFujihira

We currently have a SUMIF formula that sums up a Annualized Estimate for a specific Ministry. The Sheet with this data uses a form to populate additional rows. The currently working formula is =SUMIF(Ministry:Ministry, [Ministry Name - Annualized Estimate], [Annualized Estimate]:[Annualized Estimate]) We wanted to break this down further and add our year column to the formula to show the Annualized Estimate for a specific year rather than summing all of the Annualized Estimate for a specific Ministry. Our year column is named GAP Year and it is currently a drop down menu of 2017 and 2018. This drop down is restricted to 2017 and 2018 but we may want to make a check box that will allow us to select both 2017 and 2018.

I believe I need to change the formula to =SUMIFS but from there I cannot seem to find the correct format to include the year criterion.

Basic thought would be: Take the Annualized Estimate ($) for a specific Ministry and a specific year(s) and add them together. 

I appreciate the help.

NFujihira

I have figured out how to add the GAP Year criterion to my formula which is: =SUMIFS([Annualized Estimate]:[Annualized Estimate], Ministry:Ministry, [Ministry Name - Annualized Estimate]4, [GAP Year]:[GAP Year], "2017") but I would like to add one more criteria to this formula. I have another column named "Service Segments" which is a drop down menu of 6 different segments. How can I add this to my formula? Here is the formula that does not work: =SUMIFS([Annualized Estimate]:[Annualized Estimate], Ministry:Ministry, [Ministry Name - Annualized Estimate]4, [GAP Year]:[GAP Year], "2017", [Service Segments]:[Service Segments], “Clinical Supply Services” ) I added the underlined criteria to my formula but I am not sure what I am missing.

Thank you.

CanadaJim

Hi there.

Without seeing the data itself, or what you exactly mean when you say it doesn't work, the only reason I can see for it not to work is that there are no rows that EXACTLY match "Clinical Supply Services" in the [Service Segments] column.  There could be a trailing space in the dropdown menu item for this list.

If you can't figure out why it doesn't match, you may want to treat the service segment similarly to how you treat ministry, by selecting which service segment you would like to sum on row 4, thereby making sure that you have identical contents in the formula.

Hope this helps...

Jim