Sum multiple columns & rows

[AliAlhashim]

Dears,

I have the following table:

[TABLE="width: 377"]
<tbody>[TR]
[TD]Name/Month[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Nissan[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]841[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]26[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]


What I'm trying to do is to sum numbers in this table based on some criteria for rows and columns.

For example, I want to sum Nissan for the months of 1 & 5.

What I have reached so far is this formula: =SUMPRODUCT((A2:A4="Nissan")*(B1:E1=1)*B2:E4)

However, I'm unable to add multiple months to this formula.

Any help?

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this helps.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Name/Month	1	2	5	4		Name	Nissan
2	Nissan	100	140	120	30		Months	1,5
3	Honda	45	456	4	841		Sum	220
4	Toyota	4	7	46	26
SUM

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Name/Month[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]month[/TD]
[TD]sum[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Nissan[/TD]
[TD]

100​

[/TD]
[TD]

140​

[/TD]
[TD]

120​

[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD]Nissan[/TD]
[TD]

1​

[/TD]
[TD]

220​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Honda[/TD]
[TD]

45​

[/TD]
[TD]

456​

[/TD]
[TD]

4​

[/TD]
[TD]

841​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Toyota[/TD]
[TD]

4​

[/TD]
[TD]

7​

[/TD]
[TD]

46​

[/TD]
[TD]

26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I2=SUMPRODUCT((A2:A4=G2)*(ISNUMBER(MATCH(B1:E1,H2:H3,0))),B2:E4)

 

[AliAlhashim]

Dear Peter & MARZIOTULLIO

Thanks a lot for your quick reply, both formula works as a charm.

Do you think it's possible to use this formula to search for partial text?

For example, instead of Nissan, I want to search for "Niss"?

Best Regards,

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Name/Month[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]month[/TD]
[TD]sum[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Nissan[/TD]
[TD]

100​

[/TD]
[TD]

140​

[/TD]
[TD]

120​

[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD]Niss[/TD]
[TD]

1​

[/TD]
[TD]

220​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Honda[/TD]
[TD]

45​

[/TD]
[TD]

456​

[/TD]
[TD]

4​

[/TD]
[TD]

841​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Toyota[/TD]
[TD]

4​

[/TD]
[TD]

7​

[/TD]
[TD]

46​

[/TD]
[TD]

26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I2=SUMPRODUCT((ISNUMBER(SEARCH(G2&"*",A2:A4)))*(ISNUMBER(MATCH(B1:E1,H2:H3,0))),B2:E4)

 

[Peter_SSs]

The change for my method & layout would be

=SUMPRODUCT(--ISNUMBER((FIND(","&B1:E1&",",","&H2&","))*(SEARCH(H1,A2:A4))),B2:E4)

 

[AliAlhashim]

Thanks both of you for your great support!!

 

[Peter_SSs]

Thanks both of you for your great support!!

You are welcome.

 

[MARZIOTULLIO]

You are welcome