Sumif with 2 dimensions

[MookoTheKing]

How can I sumif using 2 dimensions? In the example I posted, I want to sumif only if the criteria is column A equals Cat and row 1 equals January. The total should be 7.



<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">January</td><td style=";">February</td><td style=";">March</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Cat</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Cat</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Dog</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">8</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

 

[dave3009]

Maybe like this

Excel Workbook
	A	B	C	D
1		January	February	March
2	Cat	5	6	7
3	Cat	2	3	5
4	Dog	1	4	8
5
6		7
Sheet2

Excel 2007

Cell Formulas
Range	Formula
B6	=SUMIF(A1:A4, "Cat", OFFSET(A1, ,MATCH("January", A1:D1, 0)-1, 3, 1))

 

[Aladin Akyurek]

How can I sumif using 2 dimensions? In the example I posted, I want to sumif only if the criteria is column A equals Cat and row 1 equals January. The total should be 7.



<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">January</td><td style=";">February</td><td style=";">March</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Cat</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Cat</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Dog</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">8</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Also...

=SUMIF($A$2:$A$4,"Cat",INDEX($B$2:$D$4,0,MATCH("January",$B$1:$D$1,0)))

 

[MookoTheKing]

Thanks this works. But is there a different way without using offset since I am referencing an external workbook and it generates a value error when the workbook is closed?

 

[Aladin Akyurek]

Thanks this works. But is there a different way without using offset since I am referencing an external workbook and it generates a value error when the workbook is closed?

You need to switch from:

=SUMIF($A$2:$A$4,"Cat",INDEX($B$2:$D$4,0,MATCH("January",$B$1:$D$1,0)))

Either to:

=SUMPRODUCT(--($A$2:$A$4="Cat"),INDEX($B$2:$D$4,0,MATCH("January",$B$1:$D$1,0)))

Or to:

=SUM(IF($A$2:$A$4="Cat",INDEX($B$2:$D$4,0,MATCH("January",$B$1:$D$1,0))))

which needs to be confirmed with control+shift+enter, not just enter.

 

[Legacy 167133]

If you are referencing an external workbook, then use what Aladin gave you.

It must work.

 

[MookoTheKing]

Thanks it works now when workbook is closed.

 

[Aladin Akyurek]

Thanks it works now when workbook is closed.

You are welcome.

 

[hvluoto]

Dear Excel professionals,

I'm trying to summarize a table (Excel 2010) that has project start date and finish date and resourcing requirements to a several organizations. The source table looks like this:

A	B	C	D	E
Project start	Project end	Organization 1	Organization 2	Organization 3
Jan-2015	Jul-2015	1	5	10
Apr-2015	Jun-2015	2	4	8
Jan-2015	Feb-2015	3	6	9

<tbody>
</tbody>

Any ideas how could I get a summarized table with monthly summary of resourcing requirements by organization. The values in each cell explains the idea probably more than I can here explain. Like this:

	Jan-2015	Feb-2015	Mar-2015	Apr-2015	May-2015	Jun-2015	Jul-2015
Organization 1	4	4	1	3	3	3	1
Organization 2	11	11	5	9	9	9	5
Organization 3	19	19	10	18	18	18	10

<tbody>
</tbody>

Thanks a lot in advance!

 

[Aladin Akyurek]

@hvluoto

What is Jan-2015? A true date? If so, is it 1-Jan-2015? This question holds for all of your "dates"...