Share via

Vlookup combined with CountIf??

Anonymous
2022-03-02T14:39:00+00:00

Hi, not sure what I'm looking for can even be done but here goes.

I have a spreadsheet of team members on which I capture attendance. In order to qualify for events, you need to have attended a set number of classes (so between a set date and todays date, for example).

Can I use a combination of vlookup, countifs or something else entirely to count the number of check marks "P" but only within a date range e.g. if I have 3 days each week for an entire year, but I only want to count attendances between Feb and April

So my master sheet looks like this (names are made up):-

First Name Surname Full Name Date of last event No. of Attendences since last event Eligible for Event?
Lucy Dale Lucy Dale 04/01/2022
Angela Denver Angela Denver 04/01/2022
Joanne Smith Joanne Smith 04/01/2022
Micheal Jones Micheal Jones 04/01/2022

I also then have a separate sheet for capturing attendance:-

Week 1 Week 2 Week 3 Week 4 Week 5 Week 6
Wed Thu Mon Wed Thu Mon Wed Thu Mon Wed Thu Mon Wed Thu Mon Wed Thu
Full Name 05-Jan 06-Jan 10-Jan 12-Jan 13-Jan 17-Jan 19-Jan 20-Jan 24-Jan 26-Jan 27-Jan 31-Jan 02-Feb 03-Feb 07-Feb 09-Feb 10-Feb
Angela Denver P P P P P P P P
Lucy Dale P P P P P
Joanne Smith P P P P P
Micheal Jones P P P

NB. The "P" are formatted as Wingdings2 so show as ticks but I can amend this if needs be, to "X" or whatever

So No. of Attendances would need to look up the name on the second sheet, and count check marks against that person but only from the Date of Last Event to Today's date.

Is that even possible? In essence I want to set a threshold (which I can do separately) which states that a person must have attended a minimum of 12 meetings, in order to qualify for an event.

Hopefully that all makes sense? Any questions or clarification, please ask.

Thanks in advance

Lori

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2022-03-02T16:15:45+00:00

    Edited

    Hi,

    Appreciation: You explained the question really well . . Kudos!

    Please check whether the following solution is helpful:

    Image

    In Sheet1 > Formula in cell E2 is: =SUMPRODUCT(--(INDEX(Sheet2!$B$4:$R$7,MATCH($C2,Sheet2!$A$4:$A$7,0),)="P")*--(Sheet2!$B$3:$R$3>=$D2)*--(Sheet2!$B$3:$R$3<=TODAY()))

    In Sheet1 > Formula in cell F2 is: =IF(E2>=12,"Qualified","Not Eligible")

    NOTE:

    1. In the above suggested formulas, please change cell reference/ranges to suit Your requirement.

    2. Please drag the formulas down to more rows.

    Formula 1 Explanation

    1. Sheet1 - Master Sheet

    2. Sheet2 - in which Attendance is captured

    3. Sheet2!$B$4:$R$7 - Range in which You have put tick marks to record attendance.

    4. Sheet2!$A$4:$A$7 - in the worksheet in which You record attendance > range in which Students' name are written.

    5. Range Sheet2!$B$3:$R$3 - in the worksheet in which You record attendance > the range in which dates are written.

    Solution workbook link: Download Solution Workbook

    *****this link will expire in 7 days.

    Please respond if You require further assistance. I will try My best to be of help.

    If I was able to help You, please mark My response as answer and helpful.

    Thank You!

    5 people found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-03-03T10:13:17+00:00

    Thank you so much, this worked perfectly. Appreciate your help.

    Have marked as helpful and answered.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2022-03-03T11:56:19+00:00

    Hi,

    Solution workbook link: Download Solution Workbook

    Please refer to the screenshots:

    Solution 1. When the order of names is different > I changed the date in column#D > the result in column#E changes correctly.

    Image

    Solution 2. When the order of names is same > I changed the date in column#D > the result in column#E changes correctly.

    Image

    - Are the answers in the screenshots above correct?

    - Could You please share few screenshots?

    OR

    Could You please share Your workbook?

    • Please share sample workbook (via OneDrive, Dropbox, WeTransfer, etc.).

    If You need help with how to upload the file, please click on this link: Microsoft Website

    OR

    Please follow the instructions in this 2-minutes long video: Share file via WeTransfer

    Thank You!

    0 comments
  3. Anonymous
    2022-03-03T11:29:09+00:00

    Hmmmmm if I change the event date in column D, it doesn't change the figure in column E, which I need it to do, as it should only be counting attendances since the last event but it seems to be counting all of them?

    It works perfectly if the date is at the beginning of the attendance dates but if I change it to a later date, as though they've attended an event recently, it doesn't change and still counts them all.

    0 comments
  4. Anonymous
    2022-03-02T17:30:01+00:00

    Hi,

    If the order of names . . in both worksheets are same:

    In Sheet1 > Formula in cell E2 is: =COUNTIFS(Sheet4!$B4:$R4,"P",Sheet4!$B$3:$R$3,">="&$D2,Sheet4!$B$3:$R$3,"<="&TODAY())

    Solution workbook link (includes both solutions): Download Solution Workbook

    *this link will expire in 7 days.

    Please respond if You require further assistance. I will try My best to be of help.

    If I was able to help You, please mark My response as answer and helpful.

    Thank You!

    0 comments