SUMIFS drag down

[tangman]

Hi,
I have the following formula:

=SUMIFS('Future Sales'!$N$2:$N$5000,'Future Sales'!$X$2:$X$5000,"Flight Only",'Future Sales'!$F$2:$F$5000,"Apr-2019")

However i would like the date to change when i drag it down... when i drag down, i always get Apr-2019, however i would like May-2019, Jun-2019 etc...

Or is there another formula i use.

I have one tab with data on it (as per the above) and i am looking to find out how much of that data has Apr-2019 etc... on it.

Thanks in advance!

 

[FormR]

Hi, you can try like this:

=SUMIFS('Future Sales'!$N$2:$N$5000,'Future Sales'!$X$2:$X$5000,"Flight Only",'Future Sales'!$F$2:$F$5000,DATE(2019,4+(ROWS(A$1:A1)-1),1))

For robustness, change the cell references in red to the cell in which you first enter the formula before dragging down.

 

[tangman]

Thats great, thank you! it worked.

Can i make it more interesting whereby the date in the formula is taken from another cell ? ie: it is looking at A1, A2, etc....

A B
1 Apr-19 Formula here
2 May-19 Formula here
3 Jun-19 Formula here

 

[tangman]

Thats great, thank you! it worked.

Can i make it more interesting whereby the date in the formula is taken from another cell ? ie: it is looking at A1, A2, etc....

A B
1 Apr-19 Formula here
2 May-19 Formula here
3 Jun-19 Formula here

[TABLE="width: 239"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apr-19[/TD]
[TD]Formula here[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]May-19[/TD]
[TD]Formula here[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jun-19[/TD]
[TD]Formula here[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, that's easier. The formula for B1 referring to the date in A1 would be:

=SUMIFS('Future Sales'!$N$2:$N$5000,'Future Sales'!$X$2:$X$5000,"Flight Only",'Future Sales'!$F$2:$F$5000,A1)

 

[tangman]

that was easier than i thought as well!
thank you!