Excel: Sum a column in a date range (as indicated in a different column)

Question

I have the following sheet:

Column C has dates that an assignment is due, and Column H has estimates of how long each assignment will take. I would like to total the estimated durations for each week.
I put the first day of each week in Column N and the last day in Column O, and used this formula in Column P:
=SUMIFS($H$2:$H$98, $C$2:$C$98, ">N2", $C$2:$C$98, "<O2")

which seems like it should work, but as you can see all I am getting are 0's in Column P.
Column P is formatted as [h]:mm;@

Rajesh Sinha · Answer

Below shown method solves the issue: :Caveat: I've used only required columns. How it works: To get first day of the week the formula in cell F51: =C51-WEEKDAY(C51,2)+1 N.B. In Column C (Due By) dates has year, 2019. This formula uses Monday as the first day of the week, you can modify it according to popular practice in your zone, like if the week starts form Sunday then the formula should, =C51-WEEKDAY(C51,1)+1 To get last day of the week the formula in cell H51: =C51+7-WEEKDAY(C51,2) Final formula in cell I51: =SUMIFS($D$51:$D$64, $C$51:$C$64, ">="&F51, $C$51:$C$64, "<="&H51) Applied format in column I is, [h]:mm;@ N.B. Your formula has wrong syntax $C$2:$C$98, ">N2", was one of the reasons, and the second was using > & <, where it should >= & <=. Adjust cell references in the formula as needed.

Excel: Sum a column in a date range (as indicated in a different column)

One Answer

Add your own answers!

Ask a Question