OFFSET and UNIQUE/FILTER inside SUMIFS returns #VALUE! error

[heretolearnexcel]

I have the following formula in cell H4: =UNIQUE((EOMONTH(UNIQUE(LET(Return,OFFSET('Expenses Tracking'!$A$2,,,COUNTA('Expenses Tracking'!$A:$A)-1),FILTER(Return,Return>=DATE(2021,9,1)))),-1)+1)) . It returns a range of dates that meet certain conditions.

And another formula in a dynamic named range: =OFFSET('Summary Report'!$L$4,0,0,COUNTA('Summary Report'!$L:$L)-1) The L column is a range of values.

Both of the formulas are inside a SUMIFS function in cell S4: =SUMIFS(Dynamic_Net_Income,H4#,">="&DATE(R4,1,1),H4#,"<="&DATE(R4,12,31)) but it returns a #VALUE! error.

I checked the ranges of both the UNIQUE/FILTER and the OFFSET formulas, to see if maybe the error in the SUMIFS function was caused because of ranges of different sizes in each formula, since I noticed that the OFFSET formula had one more value at the end which was empty; but I modified the formula so that it omitted such value and the range was the same size as the UNIQUE/FILTER formula, and the #VALUE! error kept happening.

If I change the SUMIFS function to the following: =SUMIFS(L4:L8,H4#,">="&DATE(R4,1,1),H4#,"<="&DATE(R4,12,31)) it is now working, so I'm pretty sure the #VALUE! error I get in the original formula has to do with a mismatch in the ranges when I use the OFFSET and UNIQUE/FILTER formulas, but I'm not sure how to fix it.

I'd appreciate any help in figuring this out.

Thanks in advance.

 

[BigBeachBananas]

It's hard to follow your problem. Please use XL2BB to post a sample sheet.

 

[heretolearnexcel]

Does this help?

Presupuesto 2021.xlsx
	H				L			O			R	S
2	Income vs Expenses										Yearly Accumulated Net Income
3	Date				Net Income						Years	Net Income
4	September 2021				$12,023.77						2021	#VALUE!
5	October 2021				$319.20
6	November 2021				-$1,601.85
7	December 2021				$11,003.59
8	January 2022				$2,405.50
Summary Report

Cell Formulas
Range	Formula
H4:H8	H4	=UNIQUE((EOMONTH(UNIQUE(LET(Return,OFFSET('Expenses Tracking'!$A$2,,,COUNTA('Expenses Tracking'!$A:$A)-1),FILTER(Return,Return>=DATE(2021,9,1)))),-1)+1))
L4:L7	L4	=J4-K4
L8	L8	=IF(ISBLANK(H8),"",J8-K8)
S4	S4	=SUMIFS(Dynamic_Net_Income,H4#,">="&DATE(R4,1,1),H4#,"<="&DATE(R4,12,31))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
R4	List	=$P$4#

 

[Fluff]

Try using

=OFFSET('Summary Report'!$L$4,0,0,ROWS('Summary Report'!$H$4#))

 

[heretolearnexcel]

Try using

=OFFSET('Summary Report'!$L$4,0,0,ROWS('Summary Report'!$H$4#))

Thank you, it works! Do you know why the previous formula didn't work? I'm assuming it did have something to do with the range size, but I'm not sure what exactly was happening.

 

[Fluff]

I suspect it was different sized ranges, but without seeing your data it's impossible to tell.