Formula help: multiple sheets, Date range criteria, sum if less than 0

[joegalea13]

Hi, this is my first post, thank you in advance for any help you can give.

I have a spreadsheet with multiple sheets (1 per student)

Each student has the following 2 identical columns on their sheet

Column D = Time added or time minus (added is when they buy, minus is when they use)

Column B = date of lesson

I want to be able to sum column D if the value in column D is less than 0 (to extract lesson taken) only if that lessons was used between 2 dates. Ideally I place those 2 dates in another field to reference rather than hard code so i can make the change in 1 spot for all students.

Thanks,
Joe

 

[sriche01]

=sumifs(D:D,D:D,"<=0",B:B,"<="&[cell with later date],B:B,">="&[cell with earlier date])

Try the above formula. Note there are no length parameters on the ranges. Also, the references to the dates will include the two dates. If you want the date range to be between the dates use ">" instead of ">=" and vice versa.

 

[joegalea13]

Brilliant!!! Thank you very much. I was close but not close enough

 

[sriche01]

Glad to help!