Dragging SUMIFS function through table changes the array reference?

Hi all,

As you can see through the attached file I have a simple SUMIFS function that is totaling the times on each date. I need to populate the table in its entirety but I can't do a simple fill operation to do this automatically, and dragging the formula over changes the arrangement of array references in the formula. Why is this? And how can I fill the rest of the table without manually changing the direct cell references? Is there a way to prevent the referenced arrays from changing when I try to drag the formulas over? Or is there a better way to fill the cells?

I might add that simply creating a pivot table would not give me what I looking to achieve.

Thanks!

Why not just use a Pivot Table - see attached.

I guess your formulae aren't working since the dates on row 4 don't appear to be date numbers

I need to sum the values in the original table, after which I will be building a pivot table from there. Using a pivot table to derive the summed values doesn't allow me to then pivot from that data. Hopefully that makes sense.

The column headers are in a 'Date' format per the formatting selector - do I need to be doing something different? The formula works if I update these: I$4, and $H5 manually.

Please Login or Register  to view this content.

Don't drag.

Select N5:NF6 by hold Ctrl+Shift then press right arrow and down arrow

then
Press F2 and press Ctrl+Enter

or

Press Ctrl+R and Ctrl+D