Sumif with a Formula in the criteria range

[alpeshjain]

Hi,
I have an excel file like shown below-

Here cell Number B8 and C8 are variable, so when I change them, the some from range B2:M5 should be calculated and showed in cell D8.

I was almost able to do what I am trying to achieve, but the problem is the month names are in string and I need some YTD, so if I select "2016", "June", I need some of 2016 Jan to June.
This will work without issues if the months are in number like 1,2...12. and the formula for the same is-

=SUM(IF($A$2:$A$5=$B$8,IF($B$1:$M$1<=C8),$B$2:$M$5))).

I also have the formula to convert String month to number like - MONTH($C$8&"-1"+0) and can change above to-

=SUM(IF($A$2:$A$5=$B$8,IF($B$1:$M$1<=MONTH($C$8&"-1"+0),$B$2:$M$5)))

But I don't know how can I apply this formula on range "B1:M1".

Any direction or alternate approach is welcome.

Thanks inadvance
Alpesh

 

[alpeshjain]

Hi,
I have an excel file like shown below-

Here cell Number B8 and C8 are variable, so when I change them, the some from range B2:M5 should be calculated and showed in cell D8.

I was almost able to do what I am trying to achieve, but the problem is the month names are in string and I need some YTD, so if I select "2016", "June", I need some of 2016 Jan to June.
This will work without issues if the months are in number like 1,2...12. and the formula for the same is-

=SUM(IF($A$2:$A$5=$B$8,IF($B$1:$M$1<=C8),$B$2:$M$5))).

I also have the formula to convert String month to number like - MONTH($C$8&"-1"+0) and can change above to-

=SUM(IF($A$2:$A$5=$B$8,IF($B$1:$M$1<=MONTH($C$8&"-1"+0),$B$2:$M$5)))

But I don't know how can I apply this formula on range "B1:M1".

Any direction or alternate approach is welcome.

Thanks inadvance
Alpesh

I think the image was not uploaded,so here is the data I am working with.

A B C D E F G H I J K L M
1 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2 2015 123 234 234 2143 234 365 436 36 56 546 45 34
3 2016 2134 234 24 234 132 3546 546 64 324 1324 1243 234
4 2017 234 324 14 234 3546 234 546 56 1234 14 134 234
5 2018 14 14 14 124 324 65 3546 3456 456 24 1234 2134
6
7 Year Month YTD
8 2016 Jun 6304

 

[FormR]

I think the image was not uploaded,so here is the data I am working with.

Hi, sample data directly in the thread is much easier for your potential helpers to work with

Here is one option you can try:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	2015	123	234	234	2143	234	365	436	36	56	546	45	34
3	2016	2134	234	24	234	132	3546	546	64	324	1324	1243	234
4	2017	234	324	14	234	3546	234	546	56	1234	14	134	234
5	2018	14	14	14	124	324	65	3546	3456	456	24	1234	2134
6
7	Year	Month	YTD
8	2016	Jun	6304
Sheet1

Cell Formulas
Range	Formula
C8	=SUM(INDEX($B$2:$B$5,MATCH(A8,$A$2:$A$5,0)):INDEX($B$2:$M$5,MATCH(A8,$A$2:$A$5,0),MATCH(B8,$B$1:$M$1,0)))

 

[alpeshjain]

Perfect, I was stuck with sumif and never thought of match/index, you saved me a lot of efforts.
Thanks a lot.

PS: This was my first question on mrexcel, so next time, will post with data.

Thanks,
Alpesh

 

[Mike Szczesny]

Can you use something like this? It is not exactly what you have, but I put in a beg. month and end. month. You have to use Cntrl+Shift Enter.

=SUM(OFFSET($A$1,MATCH($A$8,$A$2:$A$5,0),MATCH($B$8,$B$1:$M$1,0),,((MATCH($C$8,$B$1:$M$1,0)-MATCH($B$8,$B$1:$M$1,0)))+1))

<tbody>
</tbody>

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2015	123	234	234	2143	234	365	436	436	56	2546	45	34
2016	2134	234	24	234	132	3546	546	546	324	1324	1234	234
2017	234	324	14	234	3546	234	234	546	1234	14	134	234
2018	14	14	14	124	324	65	65	3546	456	24	1234	2134
Year	Beg Month	End Month	YTD
2016	Jan	Jun	6304

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