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?
>Solution :
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()))))