Can my SORT & FILTER formula be rewritten to work the same without them?

[gravanoc]

I'm sure I've seen this somewhere, but my Google searches keep pulling the wrong results. Here's my formula:

=SORT(FILTER(IF(Dashboard!$B$3>Dashboard[Next Interest],Dashboard[Source.Name],""),(IF(Dashboard!$B$3>Dashboard[Next Interest],Dashboard[Source.Name],"")<>"")*(IF(Dashboard!$B$3>Dashboard[Next Interest],Dashboard[Source.Name],"")<>0)))

Simply checks that the current time via NOW() is greater than some other time, and if so pulls the data from that table column, sorts it, and filters out any blanks or zeros. Preference is to do this without VBA.

 

[Domenic]

Try to adopt the following example, which uses today's day as the criteria for simplicity . . .

A1:F6

Name	Date		Criteria	Count	Results
John	10/18/21​		1/08/22​	3​	Barbara
Abbey	2/28/22​				John
Zach	12/15/21​				Zach
Bob	1/13/22​
Barbara	11/30/21​

D2:

=TODAY()

E2:

=COUNTIF(B2:B6,"<"&D2)

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(F$2:F2)<=$E$2,INDEX($A$2:$A$6,MATCH(SMALL(IF($B$2:$B$6<$D$2,COUNTIF($A$2:$A$6,"<"&$A$2:$A$6)),ROWS(F$2:F2)),IF($B$2:$B$6<$D$2,COUNTIF($A$2:$A$6,"<"&$A$2:$A$6)),0)),"")

Hope this helps!

 

[Domenic]

Actually, you did say that your data contains blanks . . .

A1:F11

Name	Date		Criteria	Count	Results
John	10/18/21​		1/09/22​	3​	Barbara
					John
					Zach
Abbey	2/28/22​
Zach	12/15/21​
Bob	1/13/22​
Barbara	11/30/21​

D2:

=TODAY()

E2:

=COUNTIF(B2:B11,"<"&D2)

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(F$2:F2)<=$E$2,INDEX($A$2:$A$11,MATCH(SMALL(IF($B$2:$B$11<>"",IF($B$2:$B$11<$D$2,COUNTIF($A$2:$A$11,"<"&$A$2:$A$11))),ROWS(F$2:F2)),IF($B$2:$B$11<>"",IF($B$2:$B$11<$D$2,COUNTIF($A$2:$A$11,"<"&$A$2:$A$11))),0)),"")