Countifs formula across multiple sheets

[jme_4477]

I need to compare student IDs on sheets Adds Drops (Column B) to Sheet2 (Column C), and then cross-reference that with the date on both sheets: Adds Drops (Column B) and Sheets2 (Column I). If the dates on Sheets2 (Column I) is more than 7 days later than the date on Adds Drops (Column B) for any student ID, I need a total. So if any students have not been contacted in more than 7 days, it should just give me the total number of students with no contact. This total will appear on a different sheet all together.

Here's a link to the file:

https://drive.google.com/file/d/1JpR...ew?usp=sharing

 

[Toadstool]

I note you've entered the formulae with Ctrl-Shift-Enter but they're not array formulae so that's not necessary.

Change the G2 formula to this and copy down:

=IFERROR(IF(OR(INDEX(Sheet2!$J$2:$J$300,MATCH(B2,Sheet2!$C$2:$C$300,0))="",INDEX(Sheet2!$J$2:$J$300,MATCH(B2,Sheet2!$C$2:$C$300,0))-7 > A2),1,0),0)

 

[jme_4477]

You are a GENIUS!!!!! Thank you SOOOOOOOOOOOOOOOOOO much!!!