gotzilla00
New Member
- Joined
- May 19, 2021
- Messages
- 5
- Office Version
- 2019
- Platform
- Windows
Hello, I have a few question about sumproduct formula here:
On the Data! sheet I use [=IF(COUNTIF(C9,"*+*"),RIGHT(C9,LEN(C9)-FIND("+",SUBSTITUTE(C9,"+","",2))),"")] in D9 to extract overtime period from adjacent cell (C9)
and from this point I've use [=SUMPRODUCT(--(ISNUMBER(SEARCH("*"&$A5&"*", Data!$C$8:$I$15))),Data!$D$8:$J$15)] in Summary! sheet to sum only overtime period (D:D,F:F,H:H,J:J) that appeared in Data! sheet containing text in A5
but the formula resulted in 0 (btw in googlesheet its return 20) so, what am I missing ? <- first question here
Therefore, The major question is how can I exclude weekends, holiday (adding 2nd criteria based on C2:J2) to this formula [=SUMPRODUCT(--(ISNUMBER(SEARCH("*"&$A5&"*", Data!$C$8:$I$15))),Data!$D$8:$J$15)] 'cause this formula summed all of overtime period based only text in A5
Example here: Sample.xlsx
Thank you,
On the Data! sheet I use [=IF(COUNTIF(C9,"*+*"),RIGHT(C9,LEN(C9)-FIND("+",SUBSTITUTE(C9,"+","",2))),"")] in D9 to extract overtime period from adjacent cell (C9)
and from this point I've use [=SUMPRODUCT(--(ISNUMBER(SEARCH("*"&$A5&"*", Data!$C$8:$I$15))),Data!$D$8:$J$15)] in Summary! sheet to sum only overtime period (D:D,F:F,H:H,J:J) that appeared in Data! sheet containing text in A5
but the formula resulted in 0 (btw in googlesheet its return 20) so, what am I missing ? <- first question here
Therefore, The major question is how can I exclude weekends, holiday (adding 2nd criteria based on C2:J2) to this formula [=SUMPRODUCT(--(ISNUMBER(SEARCH("*"&$A5&"*", Data!$C$8:$I$15))),Data!$D$8:$J$15)] 'cause this formula summed all of overtime period based only text in A5
Example here: Sample.xlsx
Thank you,