If statement with a between date condition

[Imran Azam]

Hi Guys

I have the below data

[TABLE="width: 50"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]MANAGER[/TD]
[TD]DATE[/TD]
[TD]STATUS[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Roy[/TD]
[TD]01/01/2019[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Roy[/TD]
[TD]01/02/2019[/TD]
[TD]Won[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Roy[/TD]
[TD]20/02/2019[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Roy[/TD]
[TD]01/01/2019[/TD]
[TD]Won[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Roy[/TD]
[TD]01/01/2019[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Roy[/TD]
[TD]11/02/2019[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Roy[/TD]
[TD]01/01/2019[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Roy[/TD]
[TD]01/01/2019[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Roy[/TD]
[TD]01/01/2019[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Roy[/TD]
[TD]01/01/2019[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Eric[/TD]
[TD]01/01/2019[/TD]
[TD]Open[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Eric[/TD]
[TD]01/01/2019[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Eric[/TD]
[TD]01/01/2019[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Eric[/TD]
[TD]01/01/2019[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Eric[/TD]
[TD]01/01/2019[/TD]
[TD]Open[/TD]
[/TR]
</tbody>[/TABLE]


I am trying to create a formula which will do the following

get the count of open status for manager Roy ( in this case 11) + the count of won status where the date is in feb 2019 for manager Roy ( in this case 1) + the number of lost status where the date is in feb 2019 for manger Roy ( in this case 1)


so the total number we get in the outcome of the formula should be 13


I have tried doing this using the if statement but haven got anywhere

can this be done?




thank you for any help provided

 

[mohsan_akhtar]

I am a bit confused on how you get your numbers from the data you provides above?

count of open status for manager Roy = 3 based on =COUNTIFS($A:$A,"Roy",$C:$C,"open")
count of won status where the date is in feb 2019 for magr roy = 1 based on =COUNTIFS($A:$A,"roy",$C:$C,"won",$B:$B,">="&$K$1,$B:$B,"<="&$K$2) [k1/k2 is start and end dates for Feb '19)
the number of lost status where the date is in feb 2019 for manger Roy = 1 based on =COUNTIFS($A:$A,"roy",$C:$C,"lost",$B:$B,">="&$K$1,$B:$B,"<="&$K$2)

You only have 10 entries for Roy total, so where do you even get a reslt of 13???


[TABLE="width: 1112"]
<colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="64" style="width: 48pt;"> <col width="228" style="width: 171pt; mso-width-source: userset; mso-width-alt: 8338;"> <col width="299" style="width: 224pt; mso-width-source: userset; mso-width-alt: 10934;"> <col width="427" style="width: 320pt; mso-width-source: userset; mso-width-alt: 15616;"> <col width="64" style="width: 48pt;" span="3"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR]
[TD="width: 71, bgcolor: transparent"]MANAGER[/TD]
[TD="width: 75, bgcolor: transparent"]DATE[/TD]
[TD="width: 53, bgcolor: transparent"]STATUS[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 228, bgcolor: transparent"]count of open status for manager Roy[/TD]
[TD="width: 299, bgcolor: transparent"]count of won status where the date is in feb 2019[/TD]
[TD="width: 427, bgcolor: transparent"]the number of lost status where the date is in feb 2019 for manger Roy[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Start Date[/TD]
[TD="width: 75, bgcolor: transparent, align: right"]01/02/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Open[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]End Date[/TD]
[TD="bgcolor: transparent, align: right"]28/02/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy[/TD]
[TD="bgcolor: transparent, align: right"]01/02/2019[/TD]
[TD="bgcolor: transparent"]Won[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy[/TD]
[TD="bgcolor: transparent, align: right"]20/02/2019[/TD]
[TD="bgcolor: transparent"]Open[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Won[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Open[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy[/TD]
[TD="bgcolor: transparent, align: right"]11/02/2019[/TD]
[TD="bgcolor: transparent"]Lost[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Lost[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Other[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Lost[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Other[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Eric[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Open[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Eric[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Lost[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Eric[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Lost[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Eric[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Other[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Eric[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="bgcolor: transparent"]Open[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]