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?

Category	Other Income
Row Labels	Sum of Value
Jan	1281​
Feb	1525​
Mar	1472​
Apr	1456​
May	1612​
Jun	1621​
Jul	1941​
Aug	1437​
Sep	1695​
Oct	1619​
Nov	1048​
Dec	1891​
Grand Total	18598​

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