Auto fill Sumifs not progressing

[ratchet1369]

Morning all,

I've setup a Sumifs to compare dates and split the sum value based on whether it is before or after the date. The date is in column sheet2'!BZ and cell sheet1'!x5. This works on a single line however when I autofill this to the bottom of the spreadsheet, the formula won't progress as X5 within the quote; it remains x5 as a constant. Suggestions on how to make it copy down and have it progress to x6, x7, x8, etc?

=sum(sumifs('sheet2'!$AX:AX,...........'sheet2'!BZ:BZ,{">'sheet1'!x5","='sheet1'x5"}))

the other formula would just be a single ">x5" but has the same issue.

 

[sandy666]

you can try

{">"&'sheet1'!x5,"="&'sheet1'x5}
or
{">"&'sheet1'!x5,'sheet1'x5}

 

[Marcelo Branco]

Try

=sumifs('sheet2'!$AX:AX,...........'sheet2'!BZ:BZ,">="&'sheet1'!x5)

M.