If statement with a between date condition

[Imran Azam]

Hi Guys

I have the below data

	A	B	C
1	MANAGER	DATE	STATUS
2	Roy	01/01/2019	Open
3	Roy	01/02/2019	Won
4	Roy	20/02/2019	Open
5	Roy	01/01/2019	Won
6	Roy	01/01/2019	Open
7	Roy	11/02/2019	Lost
8	Roy	01/01/2019	Lost
9	Roy	01/01/2019	Other
10	Roy	01/01/2019	Lost
11	Roy	01/01/2019	Other
12	Eric	01/01/2019	Open
13	Eric	01/01/2019	Lost
14	Eric	01/01/2019	Lost
15	Eric	01/01/2019	Other
16	Eric	01/01/2019	Open

<tbody>
</tbody>

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???

MANAGER	DATE	STATUS		count of open status for manager Roy	count of won status where the date is in feb 2019	the number of lost status where the date is in feb 2019 for manger Roy			Start Date	01/02/2019
Roy	01/01/2019	Open		3	1	1			End Date	28/02/2019
Roy	01/02/2019	Won		10
Roy	20/02/2019	Open
Roy	01/01/2019	Won
Roy	01/01/2019	Open
Roy	11/02/2019	Lost
Roy	01/01/2019	Lost
Roy	01/01/2019	Other
Roy	01/01/2019	Lost
Roy	01/01/2019	Other
Eric	01/01/2019	Open
Eric	01/01/2019	Lost
Eric	01/01/2019	Lost
Eric	01/01/2019	Other
Eric	01/01/2019	Open

<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> </tbody>