help with adding max date

[Imran Azam]

HI Guys

i have a countifs statement that is doing the on the below set of data

[TABLE="width: 265"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Status[/TD]
[TD]date[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]Qualified[/TD]
[TD="align: right"]30/03/2019[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]Qualified[/TD]
[TD="align: right"]30/05/2019[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]Qualified[/TD]
[TD="align: right"]30/04/2019[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]Not Qualified[/TD]
[TD="align: right"]29/04/2019[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Not Qualified[/TD]
[TD="align: right"]26/04/2019[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Qualified[/TD]
[TD="align: right"]26/04/2019[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Qualified[/TD]
[TD="align: right"]30/04/2019[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Not Qualified[/TD]
[TD="align: right"]30/04/2019[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Not Qualified[/TD]
[TD="align: right"]30/04/2019[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Imran Azam]

anyone?

 

[Marcelo Branco]

It's not clear what you are trying to do.
What is maxdate? What is the expected result?

M.

 

[Imran Azam]

Thank you for the reply M, sorry i didnt make sense. I want the count if formula which i added above only to work if the latest date in the date field is april 2019 ,if the latest data isnt april 2019 then return blank, so in the above example as the latest date is 30/05/2019 therefore it should return a blank

 

[Marcelo Branco]

Maybe...

=IF(AND(MONTH(MAX(C2:C10))=4,YEAR(MAX(C2:C10))=2019),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")),"")

M.

 

[Imran Azam]

thanks it worked

Maybe...

=IF(AND(MONTH(MAX(C2:C10))=4,YEAR(MAX(C2:C10))=2019),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")),"")

M.