Countifs formula across multiple sheets

J

jme_4477

New Member
Joined
Mar 24, 2016
Messages
43
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Google Drive says "Sorry, the file you have requested does not exist.
Make sure that you have the correct URL and the file exists."
 
Upvote 0
jme_4477,

You'll need to extend the Sheet2 search as far down as you may enter data but this should do what you want. In Adds Drops I added column D and copied this formula down:
=IFERROR(IF(INDEX(Sheet2!$I$2:$I$33,MATCH(B1,Sheet2!$C$2:$C$33,0))-7>A1,1,0),0)

E1 is the total you want to put on another sheet and is just: =SUM(D:D)


ABCDE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]30-Jul[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] , align: right"]2833720[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] "]Whalan, John[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]15-Jul[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] , align: right"]2827280[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] "]Harley, Bee[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]15-Jul[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] , align: right"]2787711[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] "]Spitz, Alex[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]15-Jul[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] , align: right"]2834784[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] "]Teeper, Daxter[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]15-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]2827597[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Lyning, Abby[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]18-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]2837420[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Marble, Dustin[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

</tbody>
Adds Drops​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1
[/TH]
[TD="align: left"]=IFERROR(IF(INDEX(Sheet2!$I$2:$I$33,MATCH(B1,Sheet2!$C$2:$C$33,0))-7>A1,1,0),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Regards,
Toadstool
 
Upvote 0
Oh my goodness! Thank you SO much!!!!!! I do have one question though. To extend the search in Sheet2, I would just enter in a higher number like 400 instead of 33, right?
 
Upvote 0
The dates 8/12 and 8/14 are both more than 7 days later than 15-Jul so I don't understand why Alex Spitz shouldn't be counted?

Your original request was "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." and there was no mention of column J for "WC Date" but I see you've added that in.

Columns D and E in this example are the formula I had for the WW check.
Columns M and N are the ones you added to check WC date.
I wasn't sure if you wanted the counts separate so I've added two more pairs:
  • G and H: Checks "Welcome WM" and "WC Date" and if either are more than 7 days after the Adds Drops date then it adds to the count.
  • J and K: Checks "Welcome WM" and "WC Date" and if both are more than 7 days after the Adds Drops date then it adds to the count.

Yes, to extend just change those ranges. I've changed them all to $193 in my example because that seemed to be the end of the table and the User Id column had some spaces in C196 and C200 so it seemed unsafe to go further.


dd

ABCDEFGHIJKLMN

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]WW >7days[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]WW OR WC >7days[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]WW AND WC >7days[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]WC >7days[/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] , align: right"]2833720[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] "]Whalan, John[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]8-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] , align: right"]2827280[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] "]Harley, Bee[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]15-Jul[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] , align: right"]2787711[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] "]Spitz, Alex[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]15-Jul[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] , align: right"]2834784[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6FAFC]#B6FAFC[/URL] "]Teeper, Daxter[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]15-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]2827597[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Lyning, Abby[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]18-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]2837420[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Marble, Dustin[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

</tbody>
Adds Drops​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IFERROR(IF(INDEX(Sheet2!$I$2:$I$193,MATCH(B2,Sheet2!$C$2:$C$193,0))-7>A2,1,0),0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=SUM(D:D)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IFERROR(IF(OR(INDEX(Sheet2!$I$2:$I$193,MATCH(B2,Sheet2!$C$2:$C$193,0))-7>A2,INDEX(Sheet2!$J$2:$J$193,MATCH(B2,Sheet2!$C$2:$C$193,0))-7>A2),1,0),0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=SUM(G:G)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=IFERROR(IF(AND(INDEX(Sheet2!$I$2:$I$193,MATCH(B2,Sheet2!$C$2:$C$193,0))-7>A2,INDEX(Sheet2!$J$2:$J$193,MATCH(B2,Sheet2!$C$2:$C$193,0))-7>A2),1,0),0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=SUM(J:J)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J10[/TH]
[TD="align: left"]=IFERROR(IF(AND(INDEX(Sheet2!$I$2:$I$193,MATCH(B10,Sheet2!$C$2:$C$193,0))-7>A10,INDEX(Sheet2!$J$2:$J$193,MATCH(B10,Sheet2!$C$2:$C$193,0))-7>A10),1,0),0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=IFERROR(IF(INDEX(Sheet2!$J$2:$J$193,MATCH(B2,Sheet2!$C$2:$C$193,0))-7>A2,1,0),0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N2[/TH]
[TD="align: left"]=SUM(M:M)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hello,

I'm sorry to bother you, but I have one final question. What would the formula be to count WC Date if there is no date entered for over 7 days from the date in Adds Drops? In other words, if you look at Sheet2, you can see that Bee Harley and Dustin Marble have no date entered in the WC Date column. Then if you look at Adds Drops, you can see that Bee was added on 8/8, and Dustin was added on 8/18. How can I calculate if the date entered on the Sheet2 sheet is over 7 days or if there is not date entered and it is over 7 day from the date entered on the Adds Drops sheet. You helped me with the first part last time, but I didn't realize that I also need the second part.

Here's the link to the doc:
https://drive.google.com/file/d/17eJvCj3GIUgHOq_0sHGpyUKXXHuP2JWw/view?usp=sharing

Thank you again SO much!!!


jme_4477 said:
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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
209
Lux Aeterna
L
S
  • Solved
Sumifs, Index, and Match with multiple criteria
Replies
5
Views
89
Peter_SSs
Peter_SSs
R
  • Question Question
Time sheet formulas
Replies
2
Views
374
rodnaaaaay
R
T
  • Solved
Multiple criteria COUNTIFS with distinct values
Replies
4
Views
1K
Fluff
Fluff
L
  • Question Question
How to look up data from on sheet to another when both sheets have multiple cells with the same ID number.
Replies
5
Views
295
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top