SUMIFS for Rows and Columns

[Justinian]

How do I use SUMIFS when one set of criteria is in rows and the second set is in columns? I attached an example. I want to query money spent per month based on the category chosen.


https://app.box.com/s/69r9pgnr231a3yzqyr3bvimimd6h4d4s

 

[sandy666]

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]Category[/td][td=bgcolor:#D9E1F2]Other Income[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]Row Labels[/td][td=bgcolor:#D9E1F2]Sum of Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jan[/td][td]

1281​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Feb[/td][td]

1525​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mar[/td][td]

1472​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apr[/td][td]

1456​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]May[/td][td]

1612​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jun[/td][td]

1621​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jul[/td][td]

1941​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Aug[/td][td]

1437​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sep[/td][td]

1695​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Oct[/td][td]

1619​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Nov[/td][td]

1048​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dec[/td][td]

1891​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]Grand Total[/td][td=bgcolor:#D9E1F2]

18598​

[/td][/tr]
[/table]

used: PowerQuery and PivotTable
see example file

 

[SpillerBD]

You can watch Mike "ExcelIsFun" Girvin demonstrate at https://www.youtube.com/watch?v=ogDdF6fLCx4

 

[Justinian]

So no formulaic method? I do not have Power Query.

 

[Fluff]

What is your expected outcome?
If you want the value for a particular category & particular month the you can use Index/Match

 

[Justinian]

I attached what I am trying to accomplish. I want to be able to select an expense like "Groceries" and then select a month like "August" and have the amount spent for groceries in August appear in my output cell.

https://app.box.com/s/69r9pgnr231a3yzqyr3bvimimd6h4d4s

 

[Fluff]

If you set the linked cell for listbox1 to I24
& the linked cell for Listbox2 to I25
Put this formula in I26
=INDEX(B2:M17,MATCH(I24,A2:A17,0),MATCH(LEFT(I25,3),B1:M1,0))

 

[Justinian]

That works perfectly!

Thnak you!

 

[Fluff]

You're welcome