Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Countifs formula across multiple sheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Countifs formula across multiple sheets

    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

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    251
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Countifs formula across multiple sheets

    Google Drive says "Sorry, the file you have requested does not exist.
    Make sure that you have the correct URL and the file exists."

  3. #3
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs formula across multiple sheets

    Thank you for letting me know Here's the link:
    https://drive.google.com/file/d/1JpR...ew?usp=sharing

  4. #4
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    251
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Countifs formula across multiple sheets

    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)


    A B C D E
    1 30-Jul 2833720 Whalan, John 1 3
    2 0
    3 15-Jul 2827280 Harley, Bee 1
    4 15-Jul 2787711 Spitz, Alex 1
    5 15-Jul 2834784 Teeper, Daxter 0
    6 0
    7 15-Aug 2827597 Lyning, Abby 0
    8 0
    9 18-Aug 2837420 Marble, Dustin 0
    Adds Drops

    Worksheet Formulas
    Cell Formula
    D1 =IFERROR(IF(INDEX(Sheet2!$I$2:$I$33,MATCH(B1,Sheet2!$C$2:$C$33,0))-7>A1,1,0),0)

    Regards,
    Toadstool

  5. #5
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs formula across multiple sheets

    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?

  6. #6
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs formula across multiple sheets

    Also, I was playing around with my spreadsheet, and I'm not sure it's working correctly. For example, Alex Spitz had a Welcome WM on on 8/12 (Sheet2 I4) and WC on 8/14 (Sheet2 J4). However, on the Adds Drops sheet, it's showing Alex had no Welcome WM or WC for greater than 7 days. Do you know how to fix this?

    Here's the new doc:
    https://drive.google.com/file/d/17eJ...ew?usp=sharing

  7. #7
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    251
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Countifs formula across multiple sheets

    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

    A B C D E F G H I J K L M N
    1 WW >7days WW OR WC >7days WW AND WC >7days WC >7days
    2 1-Aug 2833720 Whalan, John 1 2 1 2 1 2 1 2
    3 0 0 0 0
    4 8-Aug 2827280 Harley, Bee 0 0 0 0
    5 15-Jul 2787711 Spitz, Alex 1 1 1 1
    6 15-Jul 2834784 Teeper, Daxter 0 0 0 0
    7 0 0 0 0
    8 15-Aug 2827597 Lyning, Abby 0 0 0 0
    9 0 0 0 0
    10 18-Aug 2837420 Marble, Dustin 0 0 0 0
    Adds Drops

    Worksheet Formulas
    Cell Formula
    D2 =IFERROR(IF(INDEX(Sheet2!$I$2:$I$193,MATCH(B2,Sheet2!$C$2:$C$193,0))-7>A2,1,0),0)
    E2 =SUM(D:D)
    G2 =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)
    H2 =SUM(G:G)
    J2 =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)
    K2 =SUM(J:J)
    J10 =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)
    M2 =IFERROR(IF(INDEX(Sheet2!$J$2:$J$193,MATCH(B2,Sheet2!$C$2:$C$193,0))-7>A2,1,0),0)
    N2 =SUM(M:M)

  8. #8
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs formula across multiple sheets

    I cannot thank you enough!!! You are amazing!!!!!!!

  9. #9
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    251
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Countifs formula across multiple sheets

    You're welcome!

  10. #10
    New Member
    Join Date
    Mar 2016
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs formula across multiple sheets

    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/17eJ...ew?usp=sharing

    Thank you again SO much!!!


    [QUOTE=jme_4477;5325653]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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •