Imran Azam
Board Regular
- Joined
- Mar 15, 2011
- Messages
- 103
HI Guys
i have a countifs statement that is doing the on the below set of data
<tbody>
</tbody>
the logic i am trying to use is if Name is Alex and State = Qualified and date is between 01/01/2019 and 30/04/2019 and the maxdate is >= april 2019 then return the count of the name else return blank.
I have created this formula =COUNTIFS(A2:A10,"Alex",B2:B10,"Qualified",C2:C10,">="&DATEVALUE("01/01/2019"),C2:C10,"<="&DATEVALUE("30/04/2019"))/COUNTIFS(A2:A10,"Alex",C2:C10,">="&DATEVALUE("01/01/2019"),C2:C10,"<="&DATEVALUE("30/04/2019"))
this covers most of the logic but i dont know how i can add the maxdate logic
can this be done?
can anyone help?
Thank s
i have a countifs statement that is doing the on the below set of data
Name | Status | date |
Alex | Qualified | 30/03/2019 |
Alex | Qualified | 30/05/2019 |
Alex | Qualified | 30/04/2019 |
Alex | Not Qualified | 29/04/2019 |
James | Not Qualified | 26/04/2019 |
James | Qualified | 26/04/2019 |
James | Qualified | 30/04/2019 |
James | Not Qualified | 30/04/2019 |
James | Not Qualified | 30/04/2019 |
<tbody>
</tbody>
the logic i am trying to use is if Name is Alex and State = Qualified and date is between 01/01/2019 and 30/04/2019 and the maxdate is >= april 2019 then return the count of the name else return blank.
I have created this formula =COUNTIFS(A2:A10,"Alex",B2:B10,"Qualified",C2:C10,">="&DATEVALUE("01/01/2019"),C2:C10,"<="&DATEVALUE("30/04/2019"))/COUNTIFS(A2:A10,"Alex",C2:C10,">="&DATEVALUE("01/01/2019"),C2:C10,"<="&DATEVALUE("30/04/2019"))
this covers most of the logic but i dont know how i can add the maxdate logic
can this be done?
can anyone help?
Thank s
Last edited: