Hello team,
I have an additional requirement to my existing sheet where I will specify the period 'From' & 'To' and based on the selection, it should fetch the data.
I have changed the existing formula from =COUNTIFS(Data!$B$3:$B$40,">="&C$16,Data!$B$3:$B$40,"<"&EDATE(C$16,1),INDEX(Data!$C$3:$L$40,,MATCH($B3,Data!$C$2:$L$2,)),"x")
to
=COUNTIFS(Data!$B$3:$B$40,">="&$D$2,Data!$B$3:$B$40,"<="&EDATE($F$2,1),INDEX(Data!$C$3:$L$40,,MATCH($B7,Data!$C$2:$L$2,)),"X") but not getting the expected results. Please check the attached file in Sheet 'ResultPeriod'
Please help..
I have an additional requirement to my existing sheet where I will specify the period 'From' & 'To' and based on the selection, it should fetch the data.
I have changed the existing formula from =COUNTIFS(Data!$B$3:$B$40,">="&C$16,Data!$B$3:$B$40,"<"&EDATE(C$16,1),INDEX(Data!$C$3:$L$40,,MATCH($B3,Data!$C$2:$L$2,)),"x")
to
=COUNTIFS(Data!$B$3:$B$40,">="&$D$2,Data!$B$3:$B$40,"<="&EDATE($F$2,1),INDEX(Data!$C$3:$L$40,,MATCH($B7,Data!$C$2:$L$2,)),"X") but not getting the expected results. Please check the attached file in Sheet 'ResultPeriod'
Please help..
Sample_LeaveBook2.xlsx | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
2 | Select -- >> | Period From | 2/1/21 | Period To | 2/15/21 | ||
3 | |||||||
4 | |||||||
5 | Leave for the Period | Expected Result | |||||
6 | Name | ||||||
7 | Johan | 2 | 1 | ||||
8 | Micheal | ||||||
9 | Thomas | 3 | 2 | ||||
10 | Ahmed | 1 | |||||
11 | Frank | 1 | |||||
12 | Greg | 2 | 1 | ||||
13 | Micky | 1 | |||||
14 | Trace | 1 | 1 | ||||
15 | Lisy | 1 | |||||
16 | Saraham | 1 | 1 | ||||
17 | Total | 13 | |||||
ResultPeriod |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C7:C16 | C7 | =COUNTIFS(Data!$B$3:$B$40,">="&$D$2,Data!$B$3:$B$40,"<="&EDATE($F$2,1),INDEX(Data!$C$3:$L$40,,MATCH($B7,Data!$C$2:$L$2,)),"X") |
C17 | C17 | =SUM(C7:C16) |