# Formula help



## julievandermeulen (Dec 11, 2022)

Hours Summary - 2022-12-09T072409.705.xlsx
					

Microsoft Excel Workbook



					1drv.ms
				




I need help with a formula.

I need the totals in D55 and E55 to equal the totals in D51 and E51.

If the total in column F is > 10 and G is not blank I need to take G minus D or G minus F. I cannot figure out the best way to do that.


----------



## mmhill (Dec 11, 2022)

I'm not sure what you are trying to do.  Check out the SUMIFS function, it's probably what you want. It allows you to SUM based on multiple criteria.  Maybe you can do a SUMIFS function (for F>10 and G<>"" for G values and subtract SUMIFS function for F>10 and G<>"" for D values?


----------



## Sufiyan97 (Dec 12, 2022)

Hii

Can you please explain logic behind manually putting +10 in D51 and +0.25 in E51 SUM formulas?

Hours Summary - 2022-12-09T072409.705.xlsxABCDEFGHI1BP ONLY2December 7, 2022 (Wednesday)3First NameLast NameLocationRegOTTotalMissing41AllenBP6.506.50.552SmithBP1041463JonesBP90974BlackBP100.510.585WhiteBP1001096AllenBP10111-0.5107SmithBP7.507.50.5118JonesBP10010129BlackBP90971310WhiteBP100.2510.2510.251411AllenBP100101512SmithBP100.510.51613JonesBP10111-0.51714BlackBP100101815WhiteBP10111-0.51916AllenBP100102017SmithBP10111-0.52118JonesBP6062219BlackBP10111-0.52320WhiteBP10111-0.52421AllenBP100102522SmithBP1001032623JonesBP100.510.52724BlackBP100102825WhiteBP100102926AllenBP100103027SmithBP10111-0.53128JonesBP100103229BlackBP10111-0.53330WhiteBP10111-0.53431AllenBP100103532SmithBP100103633JonesBP100103734BlackBP100103835WhiteBP9.509.5-2.53936AllenBP6064037SmithBP101114138JonesBP100104239BlackBP1.2501.254340WhiteBP101.2511.25-0.254441AllenBP6.506.50.54542SmithBP100.2510.25-1.254643JonesBP100.7510.75-0.754744BlackBP10111-0.548Totals411.2519430.25124950Entered Hours392.2526418.2551Missing hours19-71252Total Hours411.2519430.255354559-13756Sheet1Cell FormulasRangeFormulaD48:G48D48=SUM(D2:D47)D51D51=SUM(G4+G10+G12+10+G25+G38+G44)E51E51=SUM(G9+0.25+G16+G18+G20+G22+G23+G30+G32+G33+G43+G45+G46+G47)F51F51=SUM(G4:G47)D52:F52D52=SUM(D50:D51)D55D55=SUM(IF(ISBLANK(G4:G47),"",IF(F4:F47<=10,G4:G47)))E55E55=SUM(IF(ISBLANK(G4:G47),"",IF(F4:F47>10,G4:G47-D4:D47)))


----------



## julievandermeulen (Dec 12, 2022)

Sufiyan97 said:


> Hii
> 
> Can you please explain logic behind manually putting +10 in D51 and +0.25 in E51 SUM formulas?
> 
> Hours Summary - 2022-12-09T072409.705.xlsxABCDEFGHI1BP ONLY2December 7, 2022 (Wednesday)3First NameLast NameLocationRegOTTotalMissing41AllenBP6.506.50.552SmithBP1041463JonesBP90974BlackBP100.510.585WhiteBP1001096AllenBP10111-0.5107SmithBP7.507.50.5118JonesBP10010129BlackBP90971310WhiteBP100.2510.2510.251411AllenBP100101512SmithBP100.510.51613JonesBP10111-0.51714BlackBP100101815WhiteBP10111-0.51916AllenBP100102017SmithBP10111-0.52118JonesBP6062219BlackBP10111-0.52320WhiteBP10111-0.52421AllenBP100102522SmithBP1001032623JonesBP100.510.52724BlackBP100102825WhiteBP100102926AllenBP100103027SmithBP10111-0.53128JonesBP100103229BlackBP10111-0.53330WhiteBP10111-0.53431AllenBP100103532SmithBP100103633JonesBP100103734BlackBP100103835WhiteBP9.509.5-2.53936AllenBP6064037SmithBP101114138JonesBP100104239BlackBP1.2501.254340WhiteBP101.2511.25-0.254441AllenBP6.506.50.54542SmithBP100.2510.25-1.254643JonesBP100.7510.75-0.754744BlackBP10111-0.548Totals411.2519430.25124950Entered Hours392.2526418.2551Missing hours19-71252Total Hours411.2519430.255354559-13756Sheet1Cell FormulasRangeFormulaD48:G48D48=SUM(D2:D47)D51D51=SUM(G4+G10+G12+10+G25+G38+G44)E51E51=SUM(G9+0.25+G16+G18+G20+G22+G23+G30+G32+G33+G43+G45+G46+G47)F51F51=SUM(G4:G47)D52:F52D52=SUM(D50:D51)D55D55=SUM(IF(ISBLANK(G4:G47),"",IF(F4:F47<=10,G4:G47)))E55E55=SUM(IF(ISBLANK(G4:G47),"",IF(F4:F47>10,G4:G47-D4:D47)))


For column D i put 10 in D51 for cell G13 because I'm missing 10 of the reg time hours.  I am also missing .25 of the OT hours so I put .25 in E51 for cell G13.


----------



## julievandermeulen (Dec 12, 2022)

mmhill said:


> I'm not sure what you are trying to do.  Check out the SUMIFS function, it's probably what you want. It allows you to SUM based on multiple criteria.  Maybe you can do a SUMIFS function (for F>10 and G<>"" for G values and subtract SUMIFS function for F>10 and G<>"" for D values?


I am trying to come up with a formula for column D that calculates the missing hours in column G based on if the hours are reg or OT.  If column G is empty I am not missing any hours.  If there are more than 10 hours in column F, I need to subtract G from E for dell D51 or subtract G from D for cell E51.

Column D = Reg time hours
Column E = OT hours
Column F = Total hours
Column G = number of missing hours. If a single cell in column G is blank, I am not missing any hours for that person.

Step 1.  I want to sum the reg hours missing, based on whether F >= 10, sum in D51
Step 2.  I want to sum the OT hours missing based on whether F >= 10, sum in E51

For G13, i need the 10 hours for reg hours and i need .25 hours for OT hours.


----------



## fjns (Dec 13, 2022)

Hi, see the linked file (with 4 additional columns), it might be usable for your problem...

The formulas used in the table:
I4: =IF((D4:D47<G4:G47)*(F4:F47>10),D4:D47,0)
J4: =IF((D4:D47>=G4:G47)*(F4:F47>10),G4:G47,0)
K4: =IF((D4:D47<G4:G47)*(F4:F47>10),G4:G47-D4:D47,0)
L4: =IF(F4:F47<=10,G4:G47,0)

HoursSummary.xlsx


----------



## julievandermeulen (Jan 6, 2023)

fjns said:


> Hi, see the linked file (with 4 additional columns), it might be usable for your problem...
> 
> The formulas used in the table:
> I4: =IF((D4:D47<G4:G47)*(F4:F47>10),D4:D47,0)
> ...


That is great but that creates more work having to add the 4 columns to calculate the missing hours.  I'm trying to create less work.


----------



## julievandermeulen (Sunday at 9:58 PM)

I'm still looking for help on this!


----------



## Peter_SSs (Monday at 1:25 AM)

julievandermeulen said:


> I need the totals in D55 and E55 to equal the totals in D51 and E51.


Is this what you are after?

julievandermeulen.xlsxABCDEFG3First NameLast NameLocationRegOTTotalMissing41AllenBP6.506.50.552SmithBP1041463JonesBP90974BlackBP100.510.585WhiteBP1001096AllenBP10111-0.5107SmithBP7.507.50.5118JonesBP10010129BlackBP90971310WhiteBP100.2510.2510.251411AllenBP100101512SmithBP100.510.51613JonesBP10111-0.51714BlackBP100101815WhiteBP10111-0.51916AllenBP100102017SmithBP10111-0.52118JonesBP6062219BlackBP10111-0.52320WhiteBP10111-0.52421AllenBP100102522SmithBP1001032623JonesBP100.510.52724BlackBP100102825WhiteBP100102926AllenBP100103027SmithBP10111-0.53128JonesBP100103229BlackBP10111-0.53330WhiteBP10111-0.53431AllenBP100103532SmithBP100103633JonesBP100103734BlackBP100103835WhiteBP9.509.5-2.53936AllenBP6064037SmithBP101114138JonesBP100104239BlackBP1.2501.254340WhiteBP101.2511.25-0.254441AllenBP6.506.50.54542SmithBP100.2510.25-1.254643JonesBP100.7510.75-0.754744BlackBP10111-0.548Totals411.2519430.254950Entered Hours392.2526418.2551Missing hours19-71210.2552Total Hours411.2519430.2553545519-7Sheet1Cell FormulasRangeFormulaD51D51=SUM(G4+G10+G12+10+G25+G38+G44)E51E51=SUM(G9+0.25+G16+G18+G20+G22+G23+G30+G32+G33+G43+G45+G46+G47)D55D55=SUMIF(F4:F47,"<=10",G4:G47)+SUMPRODUCT((D4:D47<G4:G47)*(F4:F47>10)*D4:D47)E55E55=SUMIFS(G4:G47,F4:F47,">10",G4:G47,"<0")+SUMPRODUCT((D4:D47<G4:G47)*(F4:F47>10)*E4:E47)


----------



## julievandermeulen (Yesterday at 9:45 PM)

Peter_SSs said:


> Is this what you are after?
> 
> julievandermeulen.xlsxABCDEFG3First NameLast NameLocationRegOTTotalMissing41AllenBP6.506.50.552SmithBP1041463JonesBP90974BlackBP100.510.585WhiteBP1001096AllenBP10111-0.5107SmithBP7.507.50.5118JonesBP10010129BlackBP90971310WhiteBP100.2510.2510.251411AllenBP100101512SmithBP100.510.51613JonesBP10111-0.51714BlackBP100101815WhiteBP10111-0.51916AllenBP100102017SmithBP10111-0.52118JonesBP6062219BlackBP10111-0.52320WhiteBP10111-0.52421AllenBP100102522SmithBP1001032623JonesBP100.510.52724BlackBP100102825WhiteBP100102926AllenBP100103027SmithBP10111-0.53128JonesBP100103229BlackBP10111-0.53330WhiteBP10111-0.53431AllenBP100103532SmithBP100103633JonesBP100103734BlackBP100103835WhiteBP9.509.5-2.53936AllenBP6064037SmithBP101114138JonesBP100104239BlackBP1.2501.254340WhiteBP101.2511.25-0.254441AllenBP6.506.50.54542SmithBP100.2510.25-1.254643JonesBP100.7510.75-0.754744BlackBP10111-0.548Totals411.2519430.254950Entered Hours392.2526418.2551Missing hours19-71210.2552Total Hours411.2519430.2553545519-7Sheet1Cell FormulasRangeFormulaD51D51=SUM(G4+G10+G12+10+G25+G38+G44)E51E51=SUM(G9+0.25+G16+G18+G20+G22+G23+G30+G32+G33+G43+G45+G46+G47)D55D55=SUMIF(F4:F47,"<=10",G4:G47)+SUMPRODUCT((D4:D47<G4:G47)*(F4:F47>10)*D4:D47)E55E55=SUMIFS(G4:G47,F4:F47,">10",G4:G47,"<0")+SUMPRODUCT((D4:D47<G4:G47)*(F4:F47>10)*E4:E47)


Almost. I feel like we are getting very close. Cell G13 is the hang up.  If that number happened to be 9.25, I need it to calculate 10 regulate time hours minus 9 missing regular time hours. Then still have the .25 as overtime hours. I just can't figure out how to get the 10 Regular hours to calculate.


----------



## julievandermeulen (Dec 11, 2022)

Hours Summary - 2022-12-09T072409.705.xlsx
					

Microsoft Excel Workbook



					1drv.ms
				




I need help with a formula.

I need the totals in D55 and E55 to equal the totals in D51 and E51.

If the total in column F is > 10 and G is not blank I need to take G minus D or G minus F. I cannot figure out the best way to do that.


----------



## Peter_SSs (Today at 12:02 AM)

julievandermeulen said:


> Cell G13 is the hang up. If that number happened to be 9.25, I need it to calculate 10 regulate time hours minus 9 missing regular time hours. Then still have the .25 as overtime hours.


So, if G13 was 9.25 and *everything else in rows 4:47 was the same as the sample above*, what would be the correct results in D55 and E55?


----------

