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 />

 

[hvluoto]

Aladin,

Yes, it refers to the whole month of January. Projects start always on the 1st of month and end the last date of month.

 

[Aladin Akyurek]

Aladin,

Yes, it refers to the whole month of January. Projects start always on the 1st of month and end the last date of month.

Is it a january date that is formatted to look like Jan-2015, if so, what date is it? Or is it just text?

 

[hvluoto]

Sorry for being unclear. Yes, it is a date 1.1.2015 that is formatted to look like Jan-2015. Thanks for being active.

 

[Aladin Akyurek]

Sorry for being unclear. Yes, it is a date 1.1.2015 that is formatted to look like Jan-2015. Thanks for being active.

And that Project end Jul-2015... What date is it?

 

[hvluoto]

Always the last of month. In case of July, it is 31.7.2015.

 

[Aladin Akyurek]

Is it a january date that is formatted to look like Jan-2015, if so, what date is it? Or is it just text?

Sorry for being unclear. Yes, it is a date 1.1.2015 that is formatted to look like Jan-2015. Thanks for being active.

And that Project end Jul-2015... What date is it?

Always the last of month. In case of July, it is 31.7.2015.

Let A:E of Sheet1 house the source data.

Let A:H of Sheet2 house the processing.

In B2 of Sheet2 enter, copy across, and down:

=SUMIFS(
  INDEX(Sheet1!$C$2:$E$4,0,MATCH($A2,Sheet1!$C$1:$E$1,0)),
  Sheet1!$A$2:$A$4,"<="&B$1,
  Sheet1!$B$2:$B$4,">="&EOMONTH(B$1,0))

 

[hvluoto]

This is amazing. Huge respect for your skills. Oh yes, works perfectly. Thank You.

 

[Aladin Akyurek]

This is amazing. Huge respect for your skills. Oh yes, works perfectly. Thank You.

You are welcome. Thanks for the kind feedback.

 

[gabchan]

Hi there, I got a similar situation, but seems can't reuse this same solution... I got some data where the columns are number of months (Jan-2014, Feb-2014, etc.) across a number of years, and the rows are some IDs (XYZ001, DFA330, etc.) and I am asked to compile a formula that could sum up the data to show the year-to-date total based on a date and ID picked in some separate cell (eg. if date picked is Sep-2014, and ID is BCF993, the formula should show the sum of all data for BCF003 from Jan-2014 to Sep-2014)... wondering if similar trick of sumif and offset can work?

 

[Aladin Akyurek]

Hi there, I got a similar situation, but seems can't reuse this same solution... I got some data where the columns are number of months (Jan-2014, Feb-2014, etc.) across a number of years, and the rows are some IDs (XYZ001, DFA330, etc.) and I am asked to compile a formula that could sum up the data to show the year-to-date total based on a date and ID picked in some separate cell (eg. if date picked is Sep-2014, and ID is BCF993, the formula should show the sum of all data for BCF003 from Jan-2014 to Sep-2014)... wondering if similar trick of sumif and offset can work?

	1-Dec-14	1-Jan-14	1-Feb-14	1-Mar-14	1-Apr-14	1-May-14	1-Jun-14		1-Apr-14	BCF003	68
XYZ001	29	22	30	23	14	17	22
DFA330	19	17	16	12	11	14	20
BCF003	26	19	23	15	11	28	12

<TBODY>
</TBODY>

A:H houses the sample data, J1:K1 the criteria.

L1:

=SUMIFS(
  INDEX($B$2:$H$4,MATCH(K1,$A$2:$A$4,0),0),
  $B$1:$H$1,">="&DATE(YEAR(J1),1,1),
  $B$1:$H$1,"<="&EOMONTH(J1,0))