SumIf Formula with multiple criterias in one column

[Vasweetheart05]

How can I do a sumifs field with multiple criterias in a column?

Example:
- the values are in row c
- the first lookup column is in row b. The lookup field is months.
- the second lookup column is in row a. The lookup field is category type (various expense type)

Desired Output: I want the total sum for "expense a" for multiple months (qtd, so may and June which is located in a table in the format of 2016-05 and 2016-06). The same formula could be used for YTD which I also have in separate cell ranges.

I'd rather not do multiple sumifs to ensure this formula works but an array also doesn't work properly. The array isnt working because the quarter months are in a table or seperare cells which can be changed based on the month selection.

Any suggestions on a formula to sum up a fieled based on two columns and one of which could have multiple options?

 

[Vasweetheart05]

Example:


The below is the Lookup table on a tab "Expenses"
[TABLE="width: 670"]
<tbody>[TR]
[TD]Expense Type[/TD]
[TD]Natural #[/TD]
[TD]Natural Code Description[/TD]
[TD]Month[/TD]
[TD][/TD]
[TD]TY $[/TD]
[/TR]
[TR]
[TD]Travel[/TD]
[TD]500000[/TD]
[TD]Domestic[/TD]
[TD]2015-01[/TD]
[TD][/TD]
[TD="align: right"]500.0[/TD]
[/TR]
[TR]
[TD]Travel[/TD]
[TD]500000[/TD]
[TD]Domestic[/TD]
[TD]2015-02[/TD]
[TD][/TD]
[TD="align: right"]500.0[/TD]
[/TR]
[TR]
[TD]Travel[/TD]
[TD]500002[/TD]
[TD]International[/TD]
[TD]2015-03[/TD]
[TD][/TD]
[TD="align: right"]1000.0[/TD]
[/TR]
[TR]
[TD]Travel[/TD]
[TD]500000[/TD]
[TD]Domestic[/TD]
[TD]2015-04[/TD]
[TD][/TD]
[TD="align: right"]500.0[/TD]
[/TR]
</tbody>[/TABLE]

The below is the date sections to determine which sum to calculate in a separate table/data range. The tab is MTD_QTD_YTD Expenses. For the example below, it's current month of 2016-04, the qtd and ytd is 2016-01 and 2016-02.
[TABLE="width: 96"]
<tbody>[TR]
[TD]QTD[/TD]
[TD]2016-01



[/TD]
[/TR]
[TR]
[TD]QTD[/TD]
[TD]2016-02[/TD]
[/TR]
[TR]
[TD]QTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD]2016-01[/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD]2016-02[/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need the SUM of Travel expenses for Q1 for Natural Code = Domestic.
I'd prefer not to do multiple sumifs plus sumifs. I also tried doing an array that equaled the table qtd fields and ytd fields but that also didn't work.

 

[Aladin Akyurek]

Something like...

=SUMIFS(Expenses!E:E,Expenses!A:A,"travel",Expenses!C:C,"domestic",Expenses!D:D,"2016-01")

 

[Vasweetheart05]

The "travel" is just one example, my dataset could have multiple different expense types so it' would be linked to a cell, not locked in text.

The same would go for 2016-01, the field would be linked to a cell range based on current month. So if the month was 2016-02, then the QTD value would be linked to 2016-01 (in c1) and 2016-02 (in c2) which will change based on the current month selected.

In conclusion, i need the fields to be linked to cell ranges since it could be multiple months within the quarter and year. Also any alternative to multiple sumifs + sumifs + sumifs would be ideal.

 

[Aladin Akyurek]

The "travel" is just one example, my dataset could have multiple different expense types so it' would be linked to a cell, not locked in text.

The same would go for 2016-01, the field would be linked to a cell range based on current month. So if the month was 2016-02, then the QTD value would be linked to 2016-01 (in c1) and 2016-02 (in c2) which will change based on the current month selected.

In conclusion, i need the fields to be linked to cell ranges since it could be multiple months within the quarter and year. Also any alternative to multiple sumifs + sumifs + sumifs would be ideal.

=SUMIFS(Expenses!E:E,Expenses!A:A,$X2,Expenses!C:C,$Y2,Expenses!D:D,Z$2)

where X2 is a value like travel, Y2 a value like domestic, and Z1 a value like 2016-2. Or...

=SUMPRODUCT(SUMIFS(Expenses!E:E,Expenses!A:A,$X2,Expenses!C:C,$Y2,Expenses!D:D,$Z2:$AA2))

 

[Vasweetheart05]

The sumproduct formula worked! Thank you!

 

[Aladin Akyurek]

The sumproduct formula worked! Thank you!

You are welcome.