Count ifs with date range

[Imran Azam]

HI Guys

i am trying to create a formula where it does a count if the date range condition is matched else bring back blank, below is the data

[TABLE="width: 290"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]State[/TD]
[TD][/TD]
[TD]Created On[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]Qualified[/TD]
[TD][/TD]
[TD="align: right"]30/04/2019[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]Qualified[/TD]
[TD][/TD]
[TD="align: right"]01/05/2019[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]Qualified[/TD]
[TD][/TD]
[TD="align: right"]28/05/2019[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]Qualified[/TD]
[TD][/TD]
[TD="align: right"]03/03/2019[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="colspan: 2"]Not Qualified[/TD]
[TD="align: right"]04/05/2019[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Qualified[/TD]
[TD][/TD]
[TD="align: right"]05/05/2019[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Qualified[/TD]
[TD][/TD]
[TD="align: right"]06/05/2019[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="colspan: 2"]Not Qualified[/TD]
[TD="align: right"]07/05/2019[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="colspan: 2"]Not Qualified[/TD]
[TD="align: right"]08/05/2019
[/TD]
[/TR]
</tbody>[/TABLE]


the logic i am trying to use is if Name is Alex and State = Qualified and created on is between 01/05/2019 and 30/05/2019( i just want may data) then return the count of the name, the formula i have done is below but it isn't working

=COUNTIFS(Name,"Alex",state,"Qualified",Created on,">=01/05/2019",D:D,Created on,"<=30/5/2019)/COUNTIF(Name,"Alex")

can anyone help with this?

thanks

 

[Scott T]

Is this what you want?
not exactly sure what you are trying to do.

Book1
	A	B	C	D
1	Name	State		Created On
2	Alex	Qualified		4/30/2019
3	Alex	Qualified		5/1/2019
4	Alex	Qualified		5/25/2019
5	Alex	Qualified		3/3/2019
6	James	Not Qualified		5/4/2019
7	James	Qualified		5/5/2019
8	James	Qualified		5/6/2019
9	James	Not Qualified		5/7/2019
10	James	Not Qualified		5/8/2019
11
12
13
14	2
Sheet1

Cell Formulas
Range	Formula
A14	=COUNTIFS(A2:A10,"Alex",B2:B10,"qualified",D2:D10,">="&DATEVALUE("5/1/19"),D2:D10,"<="&DATEVALUE("5/30/19"))

 

[Repush]

or

=SUMPRODUCT(--(A2:A10="Alex"),--(B2:B10="Qualified"),Month(C2:C10=5))

 

[Marcelo Branco]

Your formula has some errors:
A name cannot have a space, so Excel substitutes the space by a underscore
D:D is not correct
Missing a double-quote in 30/5/2015 (by the way the last day of May is 31)

See if this works
=COUNTIFS(Name,"Alex",State,"Qualified",Created_On,">=01/05/2019",Created_On,"<=31/5/2019")/COUNTIF(Name,"Alex")

Hope this helps

M.

 

[Imran Azam]

thank you

 

[Joe4]

=SUMPRODUCT(--(A2:A10="Alex"),--(B2:B10="Qualified"),Month(C2:C10=5))

If you were to adopt an approach like this, you will probably want to include another clause for the year, otherwise you would return records for the month of May for ALL years, not just necessarily 2019.

 

[Imran Azam]

or

=SUMPRODUCT(--(A2:A10="Alex"),--(B2:B10="Qualified"),Month(C2:C10=5))

for some reason this doesnt seem to pick the month up

 

[Joe4]

I think they don't have the parentheses in the correct place.
Try this:

=SUMPRODUCT(--(A2:A10="Alex"),--(B2:B10="Qualified"),--(MONTH(C2:C10)=5),--(YEAR(C2:C10)=2019))

 

[Imran Azam]

thanks what if i want the date range from 01/01.2019 to 01/05/2018, can this be done using same formula?

 

[Repush]

assuming 2018 is a typo . .

=SUMPRODUCT(--(A2:A10="Alex"),--(B2:B10="Qualified"),--(MONTH(C2:C10)<5),--(YEAR(C2:C10)=2019))