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?

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

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

 

[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