Formula for month and year-to-date data

[jyroxxor]

Excel 2013/2016

	A	B	C	D	E	F	G	H
1	Trial Balance
2
3		Jan18	Feb18	Mar18		Month	Feb18
4		$	$	$
5	Sales	100	600	1100			Month	Year-To-Date
6	Sales	200	700	1200			$	$
7	Purchase	300	800	1300		Sales	2300	3100
8	Purchase	400	900	1400
9	Sales	500	1000	1500		Month	Mar18
10
11							Month	Year-To-Date
12							$	$
13						Sales	3800	6900

<tbody>
</tbody>

Sheet1​

Hi all,

Would like to check on formula for G7 & H7.

Cell F3 is drop down menu which for me to select data for particular month.

For example, if I select Month = Mar18.
Cell G7 (monthly data) would reflect sales for Mar18, which is $3800
Cell H7 (year-to-date data) would reflect total sales from Jan18 to Mar18, which is $6900

 

[yky]

Is sales data only up to row 9?

 

[jyroxxor]

Is sales data only up to row 9?

Yes. May I know what is the dif/impact if the sales data exceed row 9?

 

[yky]

If every column has different row number, it'd be no so easy to write a formula to fit all scenarios.

Try this formula for monthly total:

=SUMIFS(INDIRECT(ADDRESS(4,MATCH(F3,A3:E3,0))&":"&ADDRESS(9,MATCH(F3,A3:E3,0))),A4:A9,"=Sales")

 

[admiral100]

Hi,

Try this : Ctrl+Shift+Enter NOT just Enter

G7 =SUMPRODUCT(SUMIF(A5:A9,"sales",INDEX(B5:D9,0,MATCH(G3,B3:D3,0))))
H7 =SUM(B5:INDEX(B5:D9,ROWS(A5:A9),SUM(--(MONTH(DATEVALUE(LEFT(G3,3)&" 1"))>=MONTH(DATEVALUE(LEFT(B3:D3,3)&" 1")))))*--(A5:A9="Sales"))

	A	B	C	D	E	F	G	H
1	Trial Balance
2
3		Jan18	Feb18	Mar18		Month	Mar18
4		$	$	$
5	Sales	100	600	1100			Month	Year-To-Date
6	Sales	200	700	1200			$	$
7	Purchase	300	800	1300		Sales	3800	6900
8	Purchase	400	900	1400
9	Sales	500	1000	1500

<colgroup><col width="70" span="9" style="width:52pt"> </colgroup><tbody>
</tbody>

 

[jyroxxor]

Thanks Admiral100! It works perfectly!