Countifs using date range, "Yes", and Unique Value

Question

I am trying to get a count of things matching 3 criteria in excel. First, the date has to be within the first quarter of the year, then if the column has a "Yes" in it, third count the value from the last column only once.

Here is the formula I used to get the first 2 criteria but I couldn't figure our for the life of me to count the employee only once.

=COUNTIFS(A2:A9,">=" & DATE(2020,1,1),A2:A9,"<=" & DATE(2020,3,31),B2:B9,"Yes")

table, th, td {
  border: 1px solid black;
  text-align: center;
}

<table style="width:100%">
  <tr>
    <th>Date of review</th>
    <th>Feedback Discussed with 'ee</th>
    <th>Name of 'ee</th>
    <th># of 'ees who received feedback</th>
  </tr>
  <tr>
    <td>1/1/20</td>
    <td>Yes</td>
    <td>Tom</td>
    <td>5</td>
  </tr>
  <tr>
    <td>1/2/20</td>
    <td>Yes</td>
    <td>Tom</td>
  </tr>
  <tr>
    <td>1/15/20</td>
    <td>No</td>
    <td>Bob</td>
  </tr>  
  <tr>
    <td>1/15/20</td>
    <td>Yes</td>
    <td>Greg</td>
  </tr>  
  <tr>
    <td>1/21/20</td>
    <td>Yes</td>
    <td>Sally</td>
  </tr>
    <tr>
    <td>1/25/20</td>
    <td>Yes</td>
    <td>Sally</td>
  </tr>
    <tr>
    <td>3/1/20</td>
    <td>Np</td>
    <td>Sally</td>
  </tr>
    <tr>
    <td>4/1/20</td>
    <td>Yes</td>
    <td>Bob</td>
  </tr>
  
</table>

You might look at this: https://stackoverflow.com/questions/37992116/countifs-multiple-criteria-distinct-count — Variatus, Mar 03 '20 at 00:54
Could you be more specific? What do you mean with "third count the value from the last column only once." — Error 1004, Mar 03 '20 at 07:29
@Harun24HR = Yes, Tom, Greg and Sally all received feedback within the first quarter so the count should be 3. — BRobarge, Mar 03 '20 at 16:29
@Error1004 - Sorry, I meant the count from the "Name of Employee" column should only count values once. So Tom received feedback twice but he is only counted once as an employee who received feedback in the first quarter. — BRobarge, Mar 03 '20 at 16:30

score 0 · Answer 1 · answered Mar 03 '20 at 01:17

 =IF(COUNTIFS(A2:A9,">="&DATE(2020,1,1),A2:A9,"<="&DATE(2020,3,31),B2:B9,"Yes",C2:C9,C2)=0,0,COUNTIFS(A2:A9,">="&DATE(2020,1,1),A2:A9,"<="&DATE(2020,3,31),B2:B9,"Yes",C2:C9,C2)-(COUNTIFS(A2:A9,">="&DATE(2020,1,1),A2:A9,"<="&DATE(2020,3,31),B2:B9,"Yes",C2:C9,C2)-1))

If the count is zero, it gives you zero. If the count is 1, it subtracts zero from 1 and gives you 1. If the count is multiple, it subtracts (multiple - 1) and gives you 1

score 0 · Accepted Answer · answered Mar 03 '20 at 08:10

0

You could try the below which is a more dynamic approach.

Column D is a help column which count how many occurrence of the current Name appears above the current row with the below formula:

=COUNTIF($C$2:$C3,C3)

Cell H1 has a drop down list with the quarters Q1,Q2,Q3 & Q4
Cell H2 has a drop down list with Yes & No
Range J2:L5 is a table with the starting and ending dates of each quarter.

Formula for count =COUNTIFS($A$2:$A$9,">="&VLOOKUP($H$1,$J$2:$L$5,2,FALSE),$A$2:$A$9,"<="&VLOOKUP($H$1,$J$2:$L$5,3,FALSE),$B$2:$B$9,$H$2,$D$2:$D$9,1)

answered Mar 03 '20 at 08:10

Error 1004

7,877
3
23
46

Thank you, 1 issue. If we changed the date of A9 to the first quarter, wouldn't it be incorrect? The count would still be 3 when it should be 4 because it is not the first instance of Bob being on the list but it is the first time he was reviewed. It appears to return an incorrect answer if the first time they are on the list is not the first time they received feedback. – BRobarge Mar 03 '20 at 18:24
1

Thank you, I ended up taking your advice and decided to not try and fit all of the formula in one cell. I just extracted the data I needed out of each column and used easier formulas to get the result I needed. – BRobarge Mar 04 '20 at 20:58

Countifs using date range, "Yes", and Unique Value

2 Answers2