EXCEL Filter Function Finding Data Between Two Dates EXCLUDING Missing Data Reported as "#N/A"

Question

I have an array with multiple columns, two of which are headed as "Date" and "Steps". I wish to use the Filter Function to list the Step data between two dates EXCLUDING dates where the corresponding Steps data is not available and is recorded as #N/A

I start with =Filter(Steps,(Date>Early Date)*(Date<Later Date)) and this works great but does not exclude dates where Steps data is missing. How do I modify the filter formula to exclude Steps data recorded as "#N/A"

Or ... is there another formula which accomplishes?

Answer 1

=FILTER(G2:G12,(A2:A12>I1)*(A2:A12<J1)*(NOT(ISERROR(G2:G12))),"")

Answer 2

Your help is most appreciated. Thank you.

The dates are all available but a few dates have associated Steps data that is missing.

I tried

=Filter(Steps,(Date>Early Date)*(Date<Later Date)*(Steps<>"#N/A")

But that returned #N/A.

Answer 3

Date	Calories In	Carbs	Fat	Proteins	Cal Burned	Steps
8/25/2022	1372	51%	31%	17%	2207	1750
8/26/2022	1944	45%	40%	16%	2421	4166
8/27/2022	1518.5	53%	31%	15%	2223	1273
8/28/2022	1662	47%	38%	15%	2118	1208
8/29/2022	1927	40%	43%	18%	2263	1884
8/30/2022	1471	70%	17%	14%	2253	1310
8/31/2022	1263.1	44%	41%	15%	1914	#N/A
9/1/2022	1596.1	43%	44%	13%	2513	3476
9/2/2022	2011	63%	27%	10%	2311	2351
9/3/2022	1911	39%	44%	16%	2533	4514
9/4/2022	1317	36%	48%	16%	2293	1955

Hope this helps. FOR EXAMPLE, I want the Steps data between 8/26/2022 and 9/3/2022 excluding the date that has #N/A entered for Steps.

This is extracted from a much larger data base covering 5 years, but enough to illustrate the issue.

Answer 4

Could you share some dummy data?

Try this one.

=Filter(Steps,(if(date="#N/A",0,Date>Early Date))*(if(date="#N/A",0,Date<Later Date))

Answer 5

=Filter(Steps,(if(date="#N/A",0,Date>Early Date))*(if(date="#N/A",0,Date<Later Date))

Share some dummy data and expected result will help to write a formula.