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. [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =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))

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 842"]
<colgroup><col><col><col span="11"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]2143[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]436[/TD]
[TD="align: right"]436[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]2546[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]2134[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]3546[/TD]
[TD="align: right"]546[/TD]
[TD="align: right"]546[/TD]
[TD="align: right"]324[/TD]
[TD="align: right"]1324[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]234[/TD]
[/TR]
[TR]
[TD="align: right"]2017[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]324[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]3546[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]546[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]134[/TD]
[TD="align: right"]234[/TD]
[/TR]
[TR]
[TD="align: right"]2018[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]324[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]3546[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]2134[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]Beg Month[/TD]
[TD]End Month[/TD]
[TD]YTD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD]Jan[/TD]
[TD]Jun[/TD]
[TD="align: right"]6304[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]