Using SUMIF(S) with MATCH INDEX?!

[saalaah]

Hi all,

First time posting on this forum and i am looking for help. I have two different tables and I need to calculate total to date values based on current month so i guess i have to use sumif with index match.
table 1:

	A	B
1	current month: M3	total to date
2	100	?
3	111
4	102	?

<tbody>
</tbody>

table 2:

	A	B	C	D	E
1	current month: 3	M1	M2	M3	M4
2	100	12	123	32	43
3	101	23	234	53	55
4	103	23	45	66	54

<tbody>
</tbody>

What i am trying to do is once i change current month from 2 to 3 for example the formula will automatically calculate the summation of that row in table 2 for item 100 for example. Could you guys help me form the formula? i have tried combinations of sum/sumif/sumifs with index match but without success.

Thanks!

 

[Special-K99]

Lets get some consistency in the tables
1) In Table1:A1 change M3 to 3 like it is in Table2!A2, do not put the M in TableA1!A1 any more just make it so it says "Current Month: " followed by month number.
2) Remove the "M"s from Table2!B1:E1. We know they are months, they'll always be months, just have the month number.

3) in B2
=SUMPRODUCT((RIGHT(Table1!A2,2)+0<=Table2!B1:E1)*(Table1!A2:A4=Table2!A2:A4)*(Table2!B2:E4))

QUESTION: What happens when it gets nearer the end of the year? What will your columns say in Table2!B1:Z1 ?
Will the headers read 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4... ?
How will Excel know that the second Month 1 (the one after 12) is later than the first Month 1 ?

 

[saalaah]

Lets get some consistency in the tables
1) In Table1:A1 change M3 to 3 like it is in Table2!A2, do not put the M in TableA1!A1 any more just make it so it says "Current Month: " followed by month number.
2) Remove the "M"s from Table2!B1:E1. We know they are months, they'll always be months, just have the month number.

3) in B2
=SUMPRODUCT((RIGHT(Table1!A2,2)+0<=Table2!B1:E1)*(Table1!A2:A4=Table2!A2:A4)*(Table2!B2:E4))

QUESTION: What happens when it gets nearer the end of the year? What will your columns say in Table2!B1:Z1 ?
Will the headers read 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4... ?
How will Excel know that the second Month 1 (the one after 12) is later than the first Month 1 ?

Thanks for your reply. I just did these two table for illustration purposes i dont have M in my original sheet. as for no of months it doesnt end with 12 but continues to 45 (total 45 months), so when my sheet says i am in month 35 e.g i want to sum M1 to M35. Hope this helps!

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​
1​	4​	total to date
2​	100​	210​
3​	111​	365​
4​	102​	188​
5​
6​
7​		1​	2​	3​	4​
8​	100​	12​	123​	32​	43​
9​	111​	23​	234​	53​	55​
10​	102​	23​	45​	66​	54​

<tbody>
</tbody>

B2=SUMPRODUCT(($A$8:$A$10=$A2)*($B$7:$E$7<=$A$1)*($B$8:$E$10)) copy down

 

[Aladin Akyurek]

Sheet2 (data)

Row\Col	A​	B​	C​	D​	E​
1​	Item\Month	1​	2​	3​	4​
2​	100​	12​	123​	32​	43​
3​	101​	23​	234​	53​	55​
4​	103​	23​	45​	66​	54​

Sheet1 (processing)

Row\Col	A​	B​
1​	current month
2​	3​	total to date
3​	100​	167​
4​	111​
5​	102​

<strike></strike>
In B3 enter and copy down:

=IF(ISNUMBER(MATCH($A3,Sheet2!A:A,0)),
    SUMIFS(INDEX(Sheet2!B:E,MATCH($A3,Sheet2!A:A,0),0),Sheet2!$B$1:$E$1,">=1",Sheet2!$B$1:$E$1,"<="&$A$2),
    "")

<strike></strike>

 

[saalaah]

Sheet2 (data)

Row\Col	A​	B​	C​	D​	E​
1​	Item\Month	1​	2​	3​	4​
2​	100​	12​	123​	32​	43​
3​	101​	23​	234​	53​	55​
4​	103​	23​	45​	66​	54​

<tbody>
</tbody>

Sheet1 (processing)

Row\Col	A​	B​
1​	current month
2​	3​	total to date
3​	100​	167​
4​	111​
5​	102​

<tbody>
</tbody>

<strike></strike>
In B3 enter and copy down:

=IF(ISNUMBER(MATCH($A3,Sheet2!A:A,0)),
    SUMIFS(INDEX(Sheet2!B:E,MATCH($A3,Sheet2!A:A,0),0),Sheet2!$B$1:$E$1,">=1",Sheet2!$B$1:$E$1,"<="&$A$2),
    "")

<strike></strike>

Thanks Aladin! the logic works perfectly, however the ISNUMBER function is causing some issues on my original sheet as my (processing sheet) includes items like HT-01-01,HT-01-02 instead of 100, 102 like in the example...what do you recommend using instead?

 

[Aladin Akyurek]

Thanks Aladin! the logic works perfectly, however the ISNUMBER function is causing some issues on my original sheet as my (processing sheet) includes items like HT-01-01,HT-01-02 instead of 100, 102 like in the example...what do you recommend using instead?

MATCH always returns a number or just #N/A. So, it doesn't matter whether MATCH is matching a number like 100 or text like HT-01-01. Seeing a number ISNUMBER returns TRUE, while seeing #N/A it returns FALSE. Thus, ISNUMBER cannot be the issue. Try to elaborate a bit more on the problem you encountered...

 

[saalaah]

MATCH always returns a number or just #N/A. So, it doesn't matter whether MATCH is matching a number like 100 or text like HT-01-01. Seeing a number ISNUMBER returns TRUE, while seeing #N/A it returns FALSE. Thus, ISNUMBER cannot be the issue. Try to elaborate a bit more on the problem you encountered...

Yes thats right..anyways i managed to get my outputs correctly just needed little bit of adjustments. Thanks man and thanks for all who took the time to help on this.

 

[Aladin Akyurek]

Yes thats right..anyways i managed to get my outputs correctly just needed little bit of adjustments. Thanks man and thanks for all who took the time to help on this.

Great that you sorted it out.