How to add a condition to COUNTIFS formula to exclude certain numbers?

[Rnkhch]

Hello,

I have a COUNTIFS formula [=COUNTIFS(B2:I2,"<70")] that calculates the number of times a score is less than 70, as follows:

Dates	3/4/2019	3/11/2019	3/18/2019	3/25/2019	4/1/2019	4/8/2019	4/15/2019	etc.
Student1	71	68	78	92	79	96	73	etc.
Student2	56	71	68	75	70	81	77
Student3	79	91	89	89	94	90	92
etc.

<tbody>
</tbody>

How can I add a condition to the formula that allows to exclude scores that correspond to dates marked with an "X" within the "Calendar" sheet with the same organization as the above sheet, as follows:

Dates:	3/4/2019	3/11/2019	3/18/2019	3/25/2019	4/1/2019	4/8/2019	4/15/2019	etc.
		X			X		X

<tbody>
</tbody>

Thanks for any input!

 

[Rnkhch]

Ah, oops, thanks!

 

[Rnkhch]

So, now I'm trying to take the average of the numbers in the same setup, and I tried adapting your formula to the AVERAGEIFS function, but I'm getting the #VALUE ! error again which says "A value used in the formula is of the wrong data type":

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=AVERAGEIFS(Scores!$B$2:$I$5,Scores!$B2:$I2,"<70",INDEX(Calendar!$B$2:$I$5,MATCH(Scores!$A2,Calendar!$A$2:$A$5,0),0),"<>x")

I'm suspecting that the issue is caused by the initial range of the function "

Scores!$B$2:$I$5" ​

perhaps?

Thanks a lot!
[/FONT]

 

[Fluff]

Shouldn't that be
AVERAGEIFS(Scores!$B2:$I2,Scores!$B2:$I2

 

[Rnkhch]

Ah I see! I don't know why I was thinking the initial range refers to the entire range the formula will scan, so I was including the lower rows too.

That's it for now until I encounter other conditions

Thanks a lot!!!

 

[Fluff]

You're welcome & thanks for the feedback