Trouble with SUMIFS Function

%3CLINGO-SUB%20id%3D%22lingo-sub-1567554%22%20slang%3D%22en-US%22%3ETrouble%20with%20SUMIFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567554%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20tracking%20to%20track%20runtimes%20of%20various%20components%20of%20a%20machine.%20On%20one%20sheet%2C%20I%20enter%20the%20start%2Fstop%20times%20of%20the%20day%20and%20calculate%20the%20runtime%20of%20each%20day.%20On%20another%20sheet%2C%20I%20have%20all%20my%20parts.%20Each%20part%20has%20three%20columns%20associated%20with%20it%3A%20an%20installation%20date%2C%20a%20replacement%20date%2C%20and%20a%20lifetime.%20The%20install%20date%20is%20entered%20manually.%20The%20replacement%20date%20is%20setup%20as%20the%20today()%20function%20to%20sum%20lifetime%20cumulatively%2C%20which%20I%20will%20change%20to%20manually%20entered%20once%20I%20know%20when%20it%20is%20replaced.%20I%20want%20to%20use%20the%20SUMIFS%20function%20in%20the%20lifetime%20cell%20to%20sum%20the%20runtimes%20between%20the%20install%20and%20replace%20date.%20I%20have%20data%20for%20July%2029-31%2C%20and%20August%203.%20For%20the%20three%20dates%20in%20July%2C%20in%20works%20correctly%20and%20returns%20the%20proper%20lifetime.%20Once%20the%20date%20in%20August%20is%20added%20on%20the%20runtime%20sheet%2C%20the%20lifetime%20no%20longer%20sums%20correctly.%20I%20haven't%20anything%20online%20that%20says%20SUMIFS%20stops%20working%20across%20different%20months.%20I've%20double%20checked%20the%20formula%2C%20watched%20videos%2C%20and%20read%20numerous%20tutorials%20on%20the%20function%2C%20and%20I%20have%20been%20unable%20to%20determine%20what%20the%20problem%20is.%20Does%20anyone%20know%20what's%20going%20on%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1567554%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567574%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20SUMIFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750331%22%20target%3D%22_blank%22%3E%40tjgreene%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20a%20lot%20easier%20to%20be%20helpful%20on%20the%20specifics%20if%20you%20would%20post%20either%20the%20SUMIF%20formula%20that%20you've%20written%20OR%20the%20actual%20workbook%20you're%20using.%20Both%20would%20be%20preferable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20only%20hypothesize%20otherwise--there's%20probably%20something%20in%20the%20way%20you've%20written%20the%20formula%20or%20the%20way%20the%20dates%20(and%20times%3F)%20are%20stored--that%20is%20causing%20your%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567615%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20SUMIFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BI've%20attached%20the%20file.%20Thank%20you%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567675%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20SUMIFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567675%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750331%22%20target%3D%22_blank%22%3E%40tjgreene%3C%2FA%3E%26nbsp%3B%20I%20believe%20it%20didn't%20stop%20working%20and%20has%20nothing%20to%20do%20with%20going%20into%20Aug.%26nbsp%3B%20The%20Life%20is%20showing%20time%20in%20HH%3AMM%3ASS%20and%20you%20have%20lapped%20over%20to%20another%20day.%26nbsp%3B%20If%20you%20was%20to%20see%20%22Life%22%20in%20terms%20of%20hours%20try%20multiplying%20by%2024%20and%20display%20it%20as%20a%20number%20or%20just%20change%20the%20format%20to%20number%20and%20it%20will%20show%20Life%20in%20terms%20of%20Days%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567850%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20SUMIFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%20Thank%20you%20that's%20exactly%20what%20the%20problem%20was!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567998%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20SUMIFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567998%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750331%22%20target%3D%22_blank%22%3E%40tjgreene%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIt%20is%20also%20possible%20to%20use%20a%20custom%20format%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%5BHH%5D%3AMM%3ASS%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ewhich%20will%20display%20the%20count%20of%20hours%20beyond%2024%2C%20%3CEM%3Ee.g.%3C%2FEM%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E34%3A14%3A30%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm tracking to track runtimes of various components of a machine. On one sheet, I enter the start/stop times of the day and calculate the runtime of each day. On another sheet, I have all my parts. Each part has three columns associated with it: an installation date, a replacement date, and a lifetime. The install date is entered manually. The replacement date is setup as the today() function to sum lifetime cumulatively, which I will change to manually entered once I know when it is replaced. I want to use the SUMIFS function in the lifetime cell to sum the runtimes between the install and replace date. I have data for July 29-31, and August 3. For the three dates in July, in works correctly and returns the proper lifetime. Once the date in August is added on the runtime sheet, the lifetime no longer sums correctly. I haven't anything online that says SUMIFS stops working across different months. I've double checked the formula, watched videos, and read numerous tutorials on the function, and I have been unable to determine what the problem is. Does anyone know what's going on?

5 Replies

@tjgreene 

 

It would be a lot easier to be helpful on the specifics if you would post either the SUMIF formula that you've written OR the actual workbook you're using. Both would be preferable.

 

I can only hypothesize otherwise--there's probably something in the way you've written the formula or the way the dates (and times?) are stored--that is causing your problem.

@mathetes I've attached the file. Thank you for your help!

@tjgreene  I believe it didn't stop working and has nothing to do with going into Aug.  The Life is showing time in HH:MM:SS and you have lapped over to another day.  If you was to see "Life" in terms of hours try multiplying by 24 and display it as a number or just change the format to number and it will show Life in terms of Days

@mtarler  Thank you that's exactly what the problem was!

@tjgreene 

It is also possible to use a custom format

[HH]:MM:SS

which will display the count of hours beyond 24, e.g.

34:14:30