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

G

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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
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
Peter_SSs said:
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
Click to expand...
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
Peter_SSs said:
Does your attempted formula suggest that "Sat" in sheet 'Data' will not always be in columns C:D?
Similar for "Sun" and "Holiday"?
Click to expand...
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
Peter_SSs said:
So what is wrong with the formula that you currently have in L5 of 'Summary' in post #3 above?
Click to expand...
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
gotzilla00 said:
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.
Click to expand...
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
Peter_SSs said:
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)))
Click to expand...
Thank you sooo much!! your solutions are very helpful.?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
SUMPRODUCT with FILTER Criteria
Replies
3
Views
335
Fluff
Fluff
D
  • Solved
Summing the results of multiple cells based on other data
Replies
3
Views
101
Fluff
Fluff
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
300
mumps
mumps
O
  • Solved
Find and match header in different location for the column based on sheet name
Replies
8
Views
122
Omar M
O
P
  • Solved
Copy column values from another sheet but want to exclude some specific row name completely..
2 3
Replies
23
Views
641
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

Share this page

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