Auto fill Sumifs not progressing

ratchet1369

Board Regular
Joined
Mar 16, 2009
Messages
54
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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,778
you can try

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

Watch MrExcel Video

Forum statistics

Threads
1,102,350
Messages
5,486,349
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top