Forum Discussion
help with formula
I have a spreadsheet with several tabs of data. I am wanting to count column L for time in minutes and divide it by 60 to get hours in the time period specified on Tab Injury Prevention. My current formula looks like this:
=SUMIFS('Summary of Services'!L:L,'Summary of Services'!A:A,">"&'Injury Management'!D3)/60
However, it seems to consistantly be 180 minutes/3 hours off no matter what I try. What am I missing?
Any suggestions!?? Thanks!
Dania1215 This is the formula that checks for both begin and end date.
=SUMIFS('Summary of Services'!L2:L9988,'Summary of Services'!A2:A9988,">"&'Injury Management'!D3,'Summary of Services'!A2:A9988,"<"&'Injury Management'!F3)/60
10 Replies
- I get 5 hours, which is what I think you are getting (you didn't say exactly where your formula was, but it appears to be on the Formula Totals tab, cell A58)?
The summary of services worksheet only has dates through row 7, but has times through row 9 (though the minutes in rows 8 and 9 total 135 and not 180). Is that worksheet complete?
If that's not it, then can you clarify exactly where your formula is located and what result you are expecting?- Thank you for your response.
I am trying to total the time in COlumn L on the Summary of Services page for the time period that is typed in on the Injury Management Page. So from 2/1 to 2/28, I want to know how much time was spent in prevention. (Total formulas page-A58). Then I want to take those minutes and divide by 60 to get hours. However, when I do this manually, i get a completely different answer that is 180 minutes off.
Dania1215 Not sure what you are trying to achieve, but when I take your formula it adds up all numbers from column L where the data in column A is greater than January 1, 2020. This is equal to 300. Then the result is divided by 60. The final outcome equals 5.
What do you expect to see?
- Thank you for your response.
I am trying to total the time in COlumn L on the Summary of Services page for the time period that is typed in on the Injury Management Page. So from 2/1 to 2/28, I want to know how much time was spent in prevention. (Total formulas page-A58). Then I want to take those minutes and divide by 60 to get hours. However, when I do this manually, i get a completely different answer that is 180 minutes off.Dania1215I believe you need to check you data and date settings. In the file you uploaded, the data in the Services sheet only contains entries dated in March 2021. Your SUMIFS formula contains one criteria that checks if the dates in column A are greater than a date in D3 in the Injury Management sheet, that happens to contain the date of January 1, 2020. Hence, all entries with a date in the Services tab are after January 1, 2020, adding up to 300 minutes or 5 hours. If you want to check for a date period you need to add another set of criteria for "less than the ending date" of the period. And of course, make sure that the data contains entries that fall within the set period.
