Help with FILTER Formula

O

ozzborn

Board Regular
Joined
Sep 14, 2011
Messages
84
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I cannot use Mini-sheets our IT admin will not allow us to install outside programs.

I am using WIN365 and WIN10.

Here is a snap shot of a range, the red dates are within the 30 day window.

What I am trying to do is isolate the Topics with red dates to one result.

This next picture is my data set and it is a table named data. I have developed 2 formulas to try and isolate just the dates within 30 days. I am trying to combine the formulas into one large formula and I have not been able to do that.
1685629138864.png


=FILTER(data,(data[Iteration '#1]>=K1)*(data[Iteration '#1]<=K2),"")

The above formula returns just the dates within 30 days from Today() using the Iteration #1 column.

K1 is the Today formula = TODAY()
K2 is K1 plus 30 = K1+30

1685629332981.png


=FILTER(data,(data[Iteration '#2]>=K1)*(data[Iteration '#2]<=K2),"")

The second above formula does the same with the Iteration #2 column.
What I am trying to do is combine these two formulas into one big formula so it returns the red values to one outcome.
I cannot figure out how to combine the formulas.

One last thing how do I get the 00-Jan-00 to no be returned, and just leave the cell blank.

I am grateful for any help
Ozz
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
How about
Excel Formula: 
=FILTER(data,((data[Iteration '#1]>=K1)*(data[Iteration '#1]<=K2))+((data[Iteration '#2]>=K1)*(data[Iteration '#2]<=K2)),"")
 
Upvote 0
That worked.
Here is my current formula;
=FILTER(data,((data[Iteration '#1]>=K1)*(data[Iteration '#1]<=K2))+((data[Iteration '#2]>=K1)*(data[Iteration '#2]<=K2))+((data[Iteration '#3]>=K1)*(data[Iteration '#3]<=K2))+((data[Iteration '#4]>=K1)*(data[Iteration '#4]<=K2))+((data[Iteration '#5 ]>=K1)*(data[Iteration '#5 ]<=K2)),"")

My current range is:
Range.PNG

The above formula located in J6 below returns the range below. Works pretty good.
It is also dynamic so I can add to the range and it automatically populates the results.
Any ideas on how I can handle the "00-Jan-00" and have it return blank cells?
I would like the cells to be blank / empty.
I tried conditional formatting on the results and could get nothing to work.
Thank you,
Ozz

Result.PNG
 
Upvote 0
One option is to use
Excel Formula: 
=LET(f,FILTER(data,((data[Iteration '#1]>=K1)*(data[Iteration '#1]<=K2))+((data[Iteration '#2]>=K1)*(data[Iteration '#2]<=K2))+((data[Iteration '#3]>=K1)*(data[Iteration '#3]<=K2))+((data[Iteration '#4]>=K1)*(data[Iteration '#4]<=K2))+((data[Iteration '#5 ]>=K1)*(data[Iteration '#5 ]<=K2)),""),if(f=="","",f))
another option is to use a custom cell format.
 
Upvote 0
Solution
Fluff, that worked.
Thank you

P.S. I am curious ,can you give me a hint on the custom cell format?

Again thanks,
Ozz
 
Upvote 0
For the cell format you can use 0;-0;;@
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
=FILTER
Replies
3
Views
99
etaf
etaf
S
  • Question
Need assistance using VSTACK with CHOOSECOLS and FILTER functions
Replies
3
Views
182
sspatriots
S
L
  • Question
FILTER formula that will hide blank rows.
2 3 4
Replies
34
Views
935
lite4d
L
J
  • Solved
Filter Function
Replies
4
Views
99
JPacek
J
I
  • Question
Crafting formula
Replies
4
Views
178
iphamjames
I

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

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