Sum data based on adjacent cell in another sheet but exclude column

gotzilla00

New Member
Joined
May 19, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. 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,
 

Attachments

  • Screenshot 2021-05-20 094332.png
    Screenshot 2021-05-20 094332.png
    67.9 KB · Views: 16
  • Screenshot 2021-05-20 094438.png
    Screenshot 2021-05-20 094438.png
    61.4 KB · Views: 16

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Welcome to the MrExcel board!

First question:
The problem is that your first formula to extract the overtime is returning a Text value not a Numerical value so the SUMPRODUCT function is ignoring those values.

You can correct that by coercing the text value to numerical as follows

=IF(COUNTIF(C9,"*+*"),RIGHT(C9,LEN(C9)-FIND("+",SUBSTITUTE(C9,"+","",2)))+0,"")

However, I have suggestions to simplify both those formulas

Data sheet D9:
Excel Formula:
=IFERROR(REPLACE(C9,1,FIND("+",C9&"+"),"")+0,"")

Summary sheet S5:
Excel Formula:
=SUMIF(Data!$C$8:$I$15,"*"&$A5&"*",Data!$D$8:$J$15)


Second question:
Don't fully understand and typing out sample data to test is not appealing. I suggest that you investigate XL2BB for providing sample data to make it easier for helpers
 
Upvote 0
Welcome to the MrExcel board!

First question:
The problem is that your first formula to extract the overtime is returning a Text value not a Numerical value so the SUMPRODUCT function is ignoring those values.

You can correct that by coercing the text value to numerical as follows

=IF(COUNTIF(C9,"*+*"),RIGHT(C9,LEN(C9)-FIND("+",SUBSTITUTE(C9,"+","",2)))+0,"")

However, I have suggestions to simplify both those formulas

Data sheet D9:
Excel Formula:
=IFERROR(REPLACE(C9,1,FIND("+",C9&"+"),"")+0,"")

Summary sheet S5:
Excel Formula:
=SUMIF(Data!$C$8:$I$15,"*"&$A5&"*",Data!$D$8:$J$15)


Second question:
Don't fully understand and typing out sample data to test is not appealing. I suggest that you investigate XL2BB for providing sample data to make it easier for helpers
Thanks for the advices! It's very helpful. Sorry for vague description in the second question.

Actually, in Summary!L5 I want to sum overtime period only Saturday column (Data!D8:D14) that the adjacent cell contain value in Summary!A5 (835-21) which will resulted in 18 (4.5+4.5+4.5+4.5).
FYI: not include value in Data!J8:D14 (2) which is in Tuesday column although the adjacent cell contain same value (835-21)

btw, I've simplified the formula and that leads me to another idea to use sum with index match.

but I got stuck here -> How can I combine this formula [=SUM(INDEX(Data!$D$8:$J$15,0,MATCH("*Sat*",Data!$C$2:$J$2,0)))] with this formula [=SUMIF(Data!$C$8:$I$15,"*"&$A5&"*",Data!$D$8:$J$15)]

Sample.xlsx
ABCDEFGHIJKLMNOPQRS
1Man-dayMAY
2I/O no.Day (Day)Sum (Day)Overtime in Working day (Man*Hour)Extra Day in SAT (Day)Working Time in SAT (Man*Hour)Overtime in SAT (Man*Hour)Extra Day in SUN (Day)Working Time in SUN (Man*Hour)Overtime in SUN (Man*Hour)Extra Day in Holiday (Day)Overtime in Holiday (Man*Hour)Overtime in Holiday (Man*Hour)SUM Overtime (Man*Hour)
3
4TKPLWKJLCAVS
5835-211000000180020
6817-210011110000
7Support job 0
8Leave0
9Sum man-day per month (Day)000000000000000
Summary
Cell Formulas
RangeFormula
B5,C6B5=COUNTIFS(Data!$C8:$J8,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C8:$J8,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C8:$J8,"*"&$A5&"*")
C5,D6C5=COUNTIFS(Data!$C9:$J9,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C9:$J9,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C9:$J9,"*"&$A5&"*")
D5,E6D5=COUNTIFS(Data!$C10:$J10,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C10:$J10,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C10:$J10,"*"&$A5&"*")
E5E5=COUNTIFS(Data!$C11:$J11,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C11:$J11,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C11:$J11,"*"&$A5&"*")
F5,G6F5=COUNTIFS(Data!$C13:$J13,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C13:$J13,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C13:$J13,"*"&$A5&"*")
G5G5=COUNTIFS(Data!$C14:$J14,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C14:$J14,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C14:$J14,"*"&$A5&"*")
B6B6=COUNTIFS(Data!$C8:$J8,"*"&$A6&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C8:$J8,"*"&$A6&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C8:$J8,"*"&$A6&"*")
F6F6=COUNTIFS(Data!$C13:$J13,"*"&$A6&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C13:$J13,"*"&$A6&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C13:$J13,"*"&$A6&"*")
L5L5=SUM(INDEX(Data!$D$8:$J$15,0,MATCH("*Sat*",Data!$C$2:$J$2,0)))
S5:S6S5=SUMIF(Data!$C$8:$I$15,"*"&$A5&"*",Data!$D$8:$J$15)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:S6Cell Valuecontains ""textNO


Sample.xlsx
ABCDEFGHIJ
1May-21Man-day of A & B
2NameSatSunHolidayTue
31234
4JobO.T.JobO.T.JobO.T.JobO.T.
501AM
602KP
7A Dept.
803TK   AAA 835-21 on site 2 sets+22.0
904PLAAA 835-21 on site 2 sets(8)+4.54.5  Leave 
1005WKAAA 835-21 on site 2 sets(8)+4.54.5  BBB 817-21 
1106JL   BBB 817-21 
12B Dept.
1307CAAAA 835-21 on site 2 sets(8)+4.54.5  BBB 817-21 
1408VSAAA 835-21 on site 2 sets(8)+4.54.5  BBB 817-21 
15Remark
Data
Cell Formulas
RangeFormula
D8:D11,J13:J14,H13:H14,F13:F14,D13:D14,J8:J11,H8:H11,F8:F11D8=IFERROR(REPLACE(C8,1,FIND("+",C8&"+"),"")+0,"")
 
Upvote 0
Does your attempted formula suggest that "Sat" in sheet 'Data' will not always be in columns C:D?
Similar for "Sun" and "Holiday"?
 
Upvote 0
Does your attempted formula suggest that "Sat" in sheet 'Data' will not always be in columns C:D?
Similar for "Sun" and "Holiday"?
Indeed, it will change depends on date in C3 'cause I've tie C2 with this formula [=left(text(C3,"ddd"),3)]
 
Upvote 0
So what is wrong with the formula that you currently have in L5 of 'Summary' in post #3 above?
 
Upvote 0
So what is wrong with the formula that you currently have in L5 of 'Summary' in post #3 above?
well, that formula in Summary!L5 in post#3 will returns total sum of all overtime period in Data!D8:D14 (18) even if the cell contain >1 value.

my attempt is to separate Saturday, Sunday, Holiday column from calculating overtime sum of each value in Summary!A5:A7 (in L5:7, O5:7, R5:7)

which means if columns C contain >1 value (835-21 in C9, 822-21 in C10) ->Expecting that Summary!L5 will returned 13.5 (4.5+4.5+4.5), while Summary!L6 will returned only 4.5 not both 18


Sample.xlsx
ABCDEFGHIJKLMNOPQRS
1Man-dayMAY
2I/O no.Day (Day)Sum (Day)Overtime in Working day (Man*Hour)Extra Day in SAT (Day)Working Time in SAT (Man*Hour)Overtime in SAT (Man*Hour)Extra Day in SUN (Day)Working Time in SUN (Man*Hour)Overtime in SUN (Man*Hour)Extra Day in Holiday (Day)Overtime in Holiday (Man*Hour)Overtime in Holiday (Man*Hour)SUM Overtime (Man*Hour)
3
4TKPLWKJLCAVS
5835-211000000180015.5
6822-21000000118004.5
7817-210011110000
8Support job 0
9Leave0
10Sum man-day per month (Day)000000000000000
Summary
Cell Formulas
RangeFormula
B5:B6,D7B5=COUNTIFS(Data!$C8:$J8,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C8:$J8,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C8:$J8,"*"&$A5&"*")
C5:C6,E7C5=COUNTIFS(Data!$C9:$J9,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C9:$J9,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C9:$J9,"*"&$A5&"*")
D5:D6D5=COUNTIFS(Data!$C10:$J10,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C10:$J10,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C10:$J10,"*"&$A5&"*")
E5:E6,F7E5=COUNTIFS(Data!$C11:$J11,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C11:$J11,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C11:$J11,"*"&$A5&"*")
F5:F6F5=COUNTIFS(Data!$C13:$J13,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C13:$J13,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C13:$J13,"*"&$A5&"*")
G5:G6G5=COUNTIFS(Data!$C14:$J14,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C14:$J14,"*"&$A5&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C14:$J14,"*"&$A5&"*")
B7B7=COUNTIFS(Data!$C8:$J8,"*"&$A7&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C8:$J8,"*"&$A7&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C8:$J8,"*"&$A7&"*")
C7C7=COUNTIFS(Data!$C9:$J9,"*"&$A7&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C9:$J9,"*"&$A7&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C9:$J9,"*"&$A7&"*")
G7G7=COUNTIFS(Data!$C14:$J14,"*"&$A7&"*")-COUNTIFS(Data!$C$2:$J$2,"Sat",Data!$C14:$J14,"*"&$A7&"*")-COUNTIFS(Data!$C$2:$J$2,"Sun",Data!$C14:$J14,"*"&$A7&"*")
L5:L6L5=SUM(INDEX(Data!$D$8:$J$15,0,MATCH("*Sat*",Data!$C$2:$J$2,0)))
S5:S7S5=SUMIF(Data!$C$8:$I$15,"*"&$A5&"*",Data!$D$8:$J$15)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:S7Cell Valuecontains ""textNO


Sample.xlsx
ABCDEFGHIJ
1May-21Man-day of A & B
2NameSatSunHolidayTue
31234
4JobO.T.JobO.T.JobO.T.JobO.T.
501AM
602KP
7A Dept.
803TK   AAA 835-21 on site 2 sets+22.0
904PLAAA 835-21 on site 2 sets(8)+4.54.5  Leave 
1005WKAAA 822-21 on site 2 sets(8)+4.54.5  BBB 817-21 
1106JL   BBB 817-21 
12B Dept.
1307CAAAA 835-21 on site 2 sets(8)+4.54.5  BBB 817-21 
1408VSAAA 835-21 on site 2 sets(8)+4.54.5  BBB 817-21 
15Remark
Data
Cell Formulas
RangeFormula
D8:D11,J13:J14,H13:H14,F13:F14,D13:D14,J8:J11,H8:H11,F8:F11D8=IFERROR(REPLACE(C8,1,FIND("+",C8&"+"),"")+0,"")
 
Upvote 0
well, that formula in Summary!L5 in post#3 will returns total sum of all overtime period in Data!D8:D14 (18) even if the cell contain >1 value.
Ah, yes, of course - tripped up by the small sample. :oops:

Try this

Excel Formula:
=SUMIF(INDEX(Data!$C$8:$I$15,0,MATCH("*Sat*",Data!$C$2:$J$2,0)),"*"&$A5&"*",INDEX(Data!$D$8:$J$15,0,MATCH("*Sat*",Data!$C$2:$J$2,0)))
 
Upvote 0
Solution
Ah, yes, of course - tripped up by the small sample. :oops:

Try this

Excel Formula:
=SUMIF(INDEX(Data!$C$8:$I$15,0,MATCH("*Sat*",Data!$C$2:$J$2,0)),"*"&$A5&"*",INDEX(Data!$D$8:$J$15,0,MATCH("*Sat*",Data!$C$2:$J$2,0)))
Thank you sooo much!! your solutions are very helpful.?
 
Upvote 0

Forum statistics

Threads
1,223,277
Messages
6,171,148
Members
452,382
Latest member
RonChand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top