Forum Discussion
help with formula
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
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.
- Ok i have redacted a lot of info in that spreadsheet. Let me see if i can upload another version for you to look at. I've tried writing it with the end data date. I have been working on this for days and I can't seem to push through it. I'm about to lose my mind. I will see if I can tag you.
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- OMG! I think i was missing the quotation and a comma. THIS WHOLE TIME! Ugh. Thank you SOOOO much! Riny_van_Eekelen
- @Ricky_van_Eekelen
I posted a different spreadsheet with all data. Can you take a look at it for me?