I am using google sheets, and trying to create a small table that shows me how many events for a single location (city) are currently on sale.

The method I'm using, is trying to search for a string in cell reference "A3" in sheet2 row 2, then count the date values in that column that are in the future.

What I thought would work this way was; =COUNTIFS(INDEX(MATCH($A3,TestMes!$A2:2,0),TestMes!5:100,0)), ">"&TODAY()

I have included an example here that anyone is welcome to edit; https://docs.google.com/spreadsheets/d/1a2rqWaYNzb2xphnv1RZOoEsKoYBRhJHdJTmIp9EGiSY/edit?usp=sharing

Where; COUNTIFS counts the number of cells in the range which are greater than today (ie; future) INDEX is the range of the column where; MATCH has searched the second row in the second sheet to identify which column to index, then Counts all the dates within that column that are also within the range 5:100, and finally outputs a count of the current live dates.

I'm getting a formula parse error, have watched 3 20 minute youtube videos, and tried all manner of combinations, im totally stumped.

Am I overcomplicating the formula?

CodePudding user response：

Added formula(s) to your sheet:

=COUNTIF(IFNA(FILTER(Sheet2!$5:$100,Sheet2!$A$2:$2=$A3)),">"&TODAY())

=BYROW(A3:A,LAMBDA(z,IF(z="",,COUNTIF(IFNA(FILTER(Sheet2!5:100,Sheet2!A2:2=z)),">"&TODAY()))))