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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Dates[/TD]
[TD]3/4/2019[/TD]
[TD]3/11/2019[/TD]
[TD]3/18/2019[/TD]
[TD]3/25/2019[/TD]
[TD]4/1/2019[/TD]
[TD]4/8/2019[/TD]
[TD]4/15/2019[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Student1[/TD]
[TD]71[/TD]
[TD]68[/TD]
[TD]78[/TD]
[TD]92[/TD]
[TD]79[/TD]
[TD]96[/TD]
[TD]73[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Student2[/TD]
[TD]56[/TD]
[TD]71[/TD]
[TD]68[/TD]
[TD]75[/TD]
[TD]70[/TD]
[TD]81[/TD]
[TD]77[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student3[/TD]
[TD]79[/TD]
[TD]91[/TD]
[TD]89[/TD]
[TD]89[/TD]
[TD]94[/TD]
[TD]90[/TD]
[TD]92[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Dates:[/TD]
[TD]3/4/2019[/TD]
[TD]3/11/2019[/TD]
[TD]3/18/2019[/TD]
[TD]3/25/2019[/TD]
[TD]4/1/2019[/TD]
[TD]4/8/2019[/TD]
[TD]4/15/2019[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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