How to sum data for YTD (range of date)?

[xinyan1129]

Hi,

My company financial year is May - Apr (subsequent year). Please refer case below, I have 2 sheets of data. For the budget column (E5), what sumproduct formula should I use to calculate that with criterias (month May-17 to Jan-18 (sheet 1, B1 to C1) and with channel (sheet 1, A5).

I use formula =SUMPRODUCT((YEAR('sheet 2'!$A$2:$A$26)<=YEAR($C$1))*(MONTH('sheet 2'!$A$2:$A$26)<=MONTH($C$13))*('sheet2'!$D$2:$D$26="BOOKSTORE")*('sheet2'!$B$2:$B$26)) but result is not accurate. Anyone can help?

[TABLE="width: 659"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Sheet 1 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]YTD[/TD]
[TD]May-17[/TD]
[TD]Jan-18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Budget[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sales Channel[/TD]
[TD]Gross Sales[/TD]
[TD]Return[/TD]
[TD]Net Sales[/TD]
[TD]Gross Sales[/TD]
[TD]Return[/TD]
[TD]Net Sales[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bookstore[/TD]
[TD] 312,045[/TD]
[TD] (24,258)[/TD]
[TD] 287,787[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]School[/TD]
[TD] 12,056[/TD]
[TD] (102)[/TD]
[TD] 11,954[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Total Net Sales[/TD]
[TD]324,101[/TD]
[TD] (24,360)[/TD]
[TD]299,741[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Gross Sales[/TD]
[TD]Return[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]May-17[/TD]
[TD] 30,508[/TD]
[TD] (7,627)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]May-17[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jun-17[/TD]
[TD] 44,411[/TD]
[TD] (11,103)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jun-17[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jul-17[/TD]
[TD] 38,924[/TD]
[TD] (9,731)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jul-17[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Aug-17[/TD]
[TD] 35,473[/TD]
[TD] (8,868)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Aug-17[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Sep-17[/TD]
[TD] 38,992[/TD]
[TD] (9,748)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Sep-17[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Oct-17[/TD]
[TD] 39,146[/TD]
[TD] (9,786)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Oct-17[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Nov-17[/TD]
[TD] 44,804[/TD]
[TD] (11,201)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Nov-17[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Dec-17[/TD]
[TD] 42,230[/TD]
[TD] (10,557)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Dec-17[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Jan-18[/TD]
[TD] 42,727[/TD]
[TD] (10,682)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Jan-18[/TD]
[TD] 16,171[/TD]
[TD] (4,851)[/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Feb-18[/TD]
[TD] 42,968[/TD]
[TD] (10,742)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Feb-18[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Mar-18[/TD]
[TD] 40,796[/TD]
[TD] (10,199)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Mar-18[/TD]
[TD] 13,831[/TD]
[TD] (4,149)[/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Apr-18[/TD]
[TD] 46,351[/TD]
[TD] (11,588)[/TD]
[TD]BOOKSTORE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Apr-18[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD]SCHOOL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Which version of excel? If 2007 or later you can try this:

=SUMIFS(Sheet2!B:B,Sheet2!$D:$D,$A5,Sheet2!$A:$A,">="&$B$1,Sheet2!$A:$A,"<="&$C$1)

I presume the numbers in the first table are incorrect?

 

[xinyan1129]

Thanks and I get the answer accurately