COUNTIFS returning 0 for every row

Hello

I am trying to see how many pop-test results my students have returned to me over the last year, part of that analysis is to see how many they've returned within a 1 month period and a 3 month period. This is a formula that was suggested to me to use for the one month period:

=COUNTIFS(INDEX($S$9:$LX$500,MATCH(A9,$A$9:$A$500,0),0),">0",$S$8:$LX$8,">="&TODAY()-31,$S$8:$LX$8,"<="&TODAY())

$S$8:$LX$8 is every date between 01/01/2021 and 31/12/2021 (students can return a result on any date of the year as they're assigned randomly)

$A$9:$A$500 is my list of student names

$S$9:$LX$500 is the range of test results relating to the date and student name.

All the responses in the rows are 0 and I don't understand why. They results are numbers with no gaps or random commas or dashes in.

Is anyone able to see what could be causing my issues?

Thanks

S

We can't see your data so it is difficult to diagnose the particular problem. It might be that your numbers are text values, or that your dates are text values.

Attach a sample Excel workbook by following the guidelines in the yellow banner at the top of the screen.

Hope this helps.

Pete

Apologies, I've hopefully attached a version with dummy data in this time.

It's the blue columns that have the offending formulas in. As far as I can tell the cells are all correctly numbers or dates.

Thanks so much in advance!

S

Put this formula in T8:

=S8+1

then copy it across as far as you need to go.

Then you can use these simplified formula in the blue areas:

K9: =COUNTIFS($S9:$LX9,">0",$S$8:$LX$8,">="&TODAY()-31,$S$8:$LX$8,"<="&TODAY())

L9: =COUNTIFS($S9:$LX9,">0",$S$8:$LX$8,">="&TODAY()-93,$S$8:$LX$8,"<="&TODAY())

Copy these down as required. You will get zeros in column K as you have no data within the last month. However, you will get some results for column L.

Hope this helps.

Pete