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

#### heretolearnexcel

##### Board Regular
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.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### heretolearnexcel

##### Board Regular
Does this help?
Presupuesto 2021.xlsx
HLORS
2Income vs ExpensesYearly Accumulated Net Income
3DateNet IncomeYearsNet Income
4September 2021\$12,023.772021#VALUE!
5October 2021\$319.20
6November 2021-\$1,601.85
7December 2021\$11,003.59
8January 2022\$2,405.50
Summary Report
Cell Formulas
RangeFormula
H4:H8H4=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:L7L4=J4-K4
L8L8=IF(ISBLANK(H8),"",J8-K8)
S4S4=SUMIFS(Dynamic_Net_Income,H4#,">="&DATE(R4,1,1),H4#,"<="&DATE(R4,12,31))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
R4List=\$P\$4#

#### Fluff

##### MrExcel MVP, Moderator
Try using
Excel Formula:
``=OFFSET('Summary Report'!\$L\$4,0,0,ROWS('Summary Report'!\$H\$4#))``

#### heretolearnexcel

##### Board Regular
Try using
Excel Formula:
``=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

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

Replies
3
Views
94
Replies
3
Views
107
Replies
13
Views
154
Replies
3
Views
184
Replies
3
Views
909

1,191,171
Messages
5,985,067
Members
439,938
Latest member
MAlhash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back