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!

 

[Fluff]

How about
=COUNTIFS(B2:I2,"<70",Calendar!B2:I2,"<>x")

 

[Rnkhch]

Sweet, thanks a lot!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Rnkhch]

What if each student has their own "Calendar" sheet criteria such as below:

[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][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student2[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student3[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/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]


Thanks a lot!

 

[Fluff]

How about
=COUNTIFS(B2:I2,"<70",INDEX(Calendar!$B$2:$I$4,MATCH(A2,Calendar!$A$2:$A$5,0),0),"<>x")

 

[Rnkhch]

Amazing!! Thanks a lot!!

I might have one more condition added based on the new score sheets I have received, so I'll post if I can't figure out myself

 

[Fluff]

You're welcome & thanks for the feedback

 

[Rnkhch]

I'm trying to use your formula from the "Main" Sheet, and I made adjustments as follows, but I'm getting the #VALUE ! error:

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

Am I missing anything? Thanks a lot!

(The scores are in the "Scores" sheet)_{^{<strike>
</strike>}}[/FONT]

 

[Fluff]

You're ranges are different sizes
=COUNTIFS(Scores!B2:G2,"<70",INDEX(Calendar!$B$2:$I$4,MATCH(Scores!A2,Calendar!$A$2:$A$5,0),0),"<>x")