SUMIFS with FILTER formula

[pman86]

[FONT=Inconsolata, monospace, arial, sans, sans-serif]I'm looking to get a little guidance from someone who may know what I'm after. I've found nearly all my solutions from this forum for all my other projects but this particular issue seems to elude me.

I'm looking to filter a list of expenses to a certain date range, then sum the expenses IF they match a certain category.

Itemization tab:

[/FONT][TABLE="width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Month[/TD]
[TD]January[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Date[/TD]
[TD]Category[/TD]
[TD]Desc[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/09[/TD]
[TD]Travel[/TD]
[TD]Uber Service[/TD]
[TD]65
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/12[/TD]
[TD]Food[/TD]
[TD]Denny's[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1/15[/TD]
[TD]Gas[/TD]
[TD]7-11 Gas[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1/15[/TD]
[TD]Food[/TD]
[TD]Walmart[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1/20[/TD]
[TD]Travel[/TD]
[TD]Uber Service[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1/25[/TD]
[TD]Food[/TD]
[TD]Gigi's[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1/30[/TD]
[TD]Gas[/TD]
[TD]Chevron[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=Inconsolata, monospace, arial, sans, sans-serif]

[/FONT]=SUMifs(filter(Itemization!D:D,month(Itemization!A:A)=month(datevalue(B1&" 1")),day(Itemization!A:A)>=5,day(Itemization!A:A)<20),Itemization!B:B,B12)

The final B12 is a reference to which category I'm picking on another sheet, change it to "=Gas" if that makes sense.

The problem I have with this formula is it's telling me the Filter list is not a "range".

I'm hoping its just really late and I've overlooked something simple. Thank you in advance for any help anyone can give!!!

 

[Noodleski]

Hi, not familiar with the filter function, but this part looks weird:

month(datevalue(B1&" 1"))

I'd guess that would result in something like "1 1" which has no match in column A.

 

[pman86]

I had actually found that formula here on this forum. I tested it out in a few ways and the &" 1" is needed to turn the name of the month, January, into a date string rather than just a word. Now its looking at "January 1", and returns the numerical number of the month, i.e. 1. This works as I change the date and returns 1-12 for January-December.