Nested FILTER function returning #VALUE error

oddzac

New Member
Joined
Aug 12, 2022
Messages
25
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Current Function:
Excel Formula:
=SUM(FILTER(FILTER(Table5[Amount],Table5[Description]=F33,0),(Table5[Date]>B37)*(Table5[Date]<=B38),0))


The Goal:
To SUM the contents of a table that have a specific name and are within the date range of the current month.

In the image below, dates in the top left are auto populated by "=Today()","=EOMONTH(B36,-1)" and "=EOMONTH(B36,0)" respectively.
All dates in this sheet are formatted as "Short Dates".
The innermost FILTER function successfully pulls the total of all table entries labelled "Weaver", but I'm having trouble filtering that result by the current date range.

1660319581564.png

"A value used in the formula is of the wrong data type."



1660319134278.png
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi & welcome to MrExcel.
You don't need to use two filters for that, you can use
Excel Formula:
=SUM(FILTER(Table5[Amount],(Table5[Description]=F33,0)*(Table5[Date]>B37)*(Table5[Date]<=B38),0))
 
Upvote 0
Solution
I appreciate the quick response and thanks for the warm reception!

I tried out the formula but it's throwing an error and seems like part of the "include" argument isn't being considered:
1660320764179.png
 
Upvote 0
Hi & welcome to MrExcel.
You don't need to use two filters for that, you can use
Excel Formula:
=SUM(FILTER(Table5[Amount],(Table5[Description]=F33,0)*(Table5[Date]>B37)*(Table5[Date]<=B38),0))
Removing the ",0" in (Table5[Description]=F33,0) fixed it! Thank you!!
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
Glad to help & thanks for the feedback.
Of Course!

Hope you're still here because I'm running into a new problem..
I'm applying a similar formula to a table of bills with due dates:

1660407466469.png


and having the formula:
Excel Formula:
=-SUM(FILTER(Table11[Amount],(Table11[Due Date]>G9)*(Table11[Due Date]<=G12),0))

Populate a negative sum of bills that fall within set dates/pay periods (in this case, G9 refers to the 19th and G12 the 26th):
1660407626064.png


This works like a charm, with the exception of date ranges like 26th-2nd:
1660407780263.png

Where "Scheduled Out" should read "$250.00", but because there are no numbers that are both >26 AND <2, it returns 0.

I tried playing with my double filter nonsense to add an additional <=2nd... but this breaks for cells that have a genuine zero:

1660407958417.png



Any thoughts?
 
Upvote 0
You would be far better of putting actual dates into the table, rather than numbers.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top