SUMIFS drag down

tangman

New Member
Joined
Mar 13, 2019
Messages
4
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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,422
Office Version
365
Platform
Windows
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

New Member
Joined
Mar 13, 2019
Messages
4
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

New Member
Joined
Mar 13, 2019
Messages
4
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
AB
1Apr-19Formula here
2May-19Formula here
3Jun-19Formula here

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,422
Office Version
365
Platform
Windows
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)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,319
Messages
5,510,563
Members
408,800
Latest member
Realexcel

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top