Sumif / Sumproduct - Help :)

[Mr Retirement]

Hi Everyone -

I'm looking to create a sum formula to capture multiple criteria and I am open for suggestions.

[TABLE="width: 25"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD]List:[/TD]
[TD]List:[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Car[/TD]
[TD]Jan[/TD]
[/TR]
[TR]
[TD]House[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Airplane[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Airplane[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Boat[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Boat[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula:[/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am looking sum the table for the criteria in the 2 lists to the right. Is there a way to do this? In the example above the sum would come to 16. I've tried various sumifs, sumproducts, etc. but I cannot figure out the solution without manually typing "Jan" or "Car". The list may change over time so I'd like it to link to those cells/range.

If VBA is the only way, I'm open to that as well.

Thanks everyone!
Mr R.

 

[VBA Geek]

If you want to go down the route of using an easy formula, my suggestion is to rearrange your data. Now you may think this would take you a long time but you can use consolidation ranges: https://support.office.com/en-us/ar...vottable-3ae257d2-ca94-49ff-a481-e9fc8adeeeb5

After you do this, your data will look like as per below and using a simple formula as shown below you can perform all kinds of sums with different criterias

Excel 2013/2016
	A	B	C	D	E	F
1		After consolidating ranges you have this				17
2	Type	Month	Value
3	Airplane	Jan	2		Type	Month
4	Airplane	Feb	3		car	jan
5	Airplane	Mar	3		airplane	mar
6	Boat	Jan	5		boat
7	Boat	Feb	5
8	Boat	Mar	3
9	Car	Jan	1
10	Car	Feb	1
11	Car	Mar	2
12	House	Jan	2
13	House	Feb	2
14	House	Mar	1
Sheet2

Cell Formulas
Range	Formula
F1	=DSUM(A2:C14,"Value",E3:F6)

 

[Marcelo Branco]

Maybe


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

Jan​

[/td][td]

Feb​

[/td][td]

Mar​

[/td][td][/td][td]

List:​

[/td][td]

List:​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Car​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td][/td][td]

Car​

[/td][td]

Jan​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

House​

[/td][td]

2​

[/td][td]

2​

[/td][td]

1​

[/td][td][/td][td]

Airplane​

[/td][td]

Mar​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Airplane​

[/td][td]

2​

[/td][td]

3​

[/td][td]

3​

[/td][td][/td][td]

Boat​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Boat​

[/td][td]

5​

[/td][td]

5​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Formula:​

[/td][td]

16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in B7
=SUMPRODUCT(ISNUMBER(MATCH(A2:A5,F2:F4,0))*ISNUMBER(MATCH(B1:D1,G2:G3,0))*B2:D5)

M.

 

[Tetra201]

Try

=SUMPRODUCT(B2:D5,ISNUMBER(MATCH(A2:A5,G2:G4,0))*ISNUMBER(MATCH(B1:D1,H2:H3,0)))

 

[markmzz]

Maybe this:

=SUMPRODUCT(COUNTIF(G2:G3,B1:D1)*COUNTIF(F2:F4,A2:A5)*B2:D5)

Markmzz