• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Adding cells that are dependent on another cell|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Adding cells that are dependent on another cell|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Adding cells that are dependent on …
sp_PrintTopic sp_TopicIcon
Adding cells that are dependent on another cell
Avatar
T Rindy

New Member
Members
Level 0
Forum Posts: 2
Member Since:
December 21, 2019
sp_UserOfflineSmall Offline
1
December 21, 2019 - 2:39 am
sp_Permalink sp_Print

I need to get a sum total that is dependent on another cell. In my example, I am totaling the number of "Standard" hours (up to a total of 40) and "Overtime" (over 40 hours) for each job number. A person may work on multiple jobs per day. We have a process in place to calculate this. No advice needed on this part.

 

More background:

1. I have tried to change the format. The bookkeeper is old and set in her ways. The format will not change.
2. We have 100's of potential job numbers so adding static cells won't be feasible. The line in which the job number is located will constantly be changing throughout the year.

The part that I am missing is the weekly total for each job number - the part highlighted in yellow. What formula needs to be put in the yellow cells in order to get the total I am looking for?

Thank you in advance for your help!

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
December 21, 2019 - 10:24 am
sp_Permalink sp_Print

Hi Rindy

There is no attachment.

Avatar
T Rindy

New Member
Members
Level 0
Forum Posts: 2
Member Since:
December 21, 2019
sp_UserOfflineSmall Offline
3
January 3, 2020 - 1:11 am
sp_Permalink sp_Print

Sorry.  Here is the attachment.

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
January 3, 2020 - 6:51 am
sp_Permalink sp_Print sp_EditHistory

Strange spreadsheet!

The first 2 days in columns B to E dont have an overtime column, but the other 4 do - is this an error or by design?

I really dont like this formula but with the constraints you specify and the inablity to change anything it might suffice

=IF(SUMIFS($E$21:$E$36,$B$21:$B$36,$F40)+SUMIFS($N$21:$N$36,$K$21:$K$36,$F40)+SUMIFS($I$21:$I$36,$F$21:$F$36,$F40)+SUMIFS($E$3:$E$18,$B$3:$B$18,$F40)+SUMIFS($N$3:$N$18,$K$3:$K$18,$F40)+SUMIFS($I$3:$I$18,$F$3:$F$18,$F40)>40,40,SUMIFS($E$21:$E$36,$B$21:$B$36,$F40)+SUMIFS($N$21:$N$36,$K$21:$K$36,$F40)+SUMIFS($I$21:$I$36,$F$21:$F$36,$F40)+SUMIFS($E$3:$E$18,$B$3:$B$18,$F40)+SUMIFS($N$3:$N$18,$K$3:$K$18,$F40)+SUMIFS($I$3:$I$18,$F$3:$F$18,$F40))

Basically it is just 6 sumifs added together - 1 for each day. Then wrapped around an if statment to max out if the standard hours are greater than 40. I got stuck at this part as i wasnt sure where the overtime hours come from (the J & O columns, excess hours in columns E,I & N or a combination of both)

For the unique list of jobs, i would run a quick macro as i find it less complicated than a formula (click the button on the right)

Sub List()

Dim op As Range
Dim opCount As Integer
Dim ip1 As Range, ip2 As Range, ip3 As Range, ip4 As Range, ip5 As Range, ip6 As Range

With Range("t:t")
.ClearContents
.ClearFormats
End With

Set ip1 = Range("b3:b18")
Set ip2 = Range("b21:b36")
Set ip3 = Range("f3:f18")
Set ip4 = Range("f21:f36")
Set ip5 = Range("k3:k18")
Set ip6 = Range("k21:k36")

opCount = 1

'Set op = Range("T" & opCount)

ip1.Copy Range("t" & opCount)
ip2.Copy Range("t" & Cells(Rows.Count, "T").End(xlUp).Row + 1)
ip3.Copy Range("t" & Cells(Rows.Count, "T").End(xlUp).Row + 1)
ip4.Copy Range("t" & Cells(Rows.Count, "T").End(xlUp).Row + 1)
ip5.Copy Range("t" & Cells(Rows.Count, "T").End(xlUp).Row + 1)
ip6.Copy Range("t" & Cells(Rows.Count, "T").End(xlUp).Row + 1)

With Range("t:t")
.RemoveDuplicates Columns:=1, Header:=xlNo
.Sort key1:=Range("T:T"), order1:=xlAscending, Header:=xlNo
End With

End Sub

As i said i dont really like either of these, but if the template cant change it might work.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
5
January 3, 2020 - 2:44 pm
sp_Permalink sp_Print

Hi Rindy

I will just make the following assumption

1) There is a column F for Overtime, so I added one.

2) Maximum total "Standard" hours is 40 (I hope that is what you meant).

You can give this a try. In cell I40 (after adding an additional column F) enter

=MIN(SUMIFS(E$3:E$37,$B$3:$B$37,$G40)+SUMIFS(J$3:J$37,$G$3:$G$37,$G40)+SUMIFS(O$3:O$37,$L$3:$L$37,$G40),40)

The MIN formula will take the lower of the 2 values (i.e. max is 40).

Don't quite understand what you wanted for the Overtime so I don't have a solution yet.

Maybe you can give us your expected answers based on your attachment.

This will make it easier for us to understand what you are expecting the output to be.

Sunny

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
January 4, 2020 - 9:27 am
sp_Permalink sp_Print

Hello,

With the current layout you can't avoid messy formulas, as you probably have guessed by now. As also already mentioned, I assume you will have a column F showing overtime for the first two days. If you only need to sum the standard and overtime hours you can use SUMPRODUCT to do that for you, else I suggest you use any other given previous suggestion.

In cell I40 (as I assume you need to add an extra column F), paste in below formula, then drag and copy as needed.

=SUMPRODUCT(($B$3:$B$18=$G40)*(E$3:E$18)+($B$21:$B$36=$G40)*(E$21:E$36)+($G$3:$G$18=$G40)*(J$3:J$18)+($G$21:$G$36=$G40)*(J$21:J$36)+($L$3:$L$18=$G40)*(O$3:O$18)+($L$21:$L$36=$G40)*(O$21:O$36))

As your data is spread out the formula is a bit hard to read and understand, but the basics are as follows:

For any job number in cells B3 to B18 matching the job number in cell G40, sum the hours in cells E3 to E18, etc.
The + symbol equals OR and the * symbol equals AND.
As SUMPRODUCT is a bit more fussy about data format you can't just ask it for example to look in range B3 to B36, as that range contains both text and numbers.

I hope you will find a working solution.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Rocco Pinneri, Alexandra Radu, AndyC
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.