help with adding max date

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

NameStatusdate
AlexQualified30/03/2019
AlexQualified30/05/2019
AlexQualified30/04/2019
AlexNot Qualified29/04/2019
JamesNot Qualified26/04/2019
JamesQualified26/04/2019
JamesQualified30/04/2019
JamesNot Qualified30/04/2019
JamesNot Qualified30/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:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
It's not clear what you are trying to do.
What is maxdate? What is the expected result?

M.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top