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

 

[gabchan]

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

Thanks so much for your prompt response. I tried and it works for the first row... so this is only good for unique IDs? what if there are duplicated IDs within... we can sort them up nicely, but this still makes it only good for the first row only.... Thanks in advance.

 

[Aladin Akyurek]

Thanks so much for your prompt response. I tried and it works for the first row... so this is only good for unique IDs? what if there are duplicated IDs within... we can sort them up nicely, but this still makes it only good for the first row only.... Thanks in advance.

That makes it a different problem, not one for SUMIFS...

Control+shift+enter, not just enter:

=SUM(
  IF($A$2:$A$4=K1,
    IF($B$1:$H$1>=DATE(YEAR(J1),1,1),
  IF($B$1:$H$1<=EOMONTH(J1,0),
   $B$2:$H$4))))

 

[gabchan]

That makes it a different problem, not one for SUMIFS...

Control+shift+enter, not just enter:

=SUM(
  IF($A$2:$A$4=K1,
    IF($B$1:$H$1>=DATE(YEAR(J1),1,1),
  IF($B$1:$H$1<=EOMONTH(J1,0),
   $B$2:$H$4))))

YOU SAVED MY DAY!!! BIG THANKS!! I only thought of using sumifs and maybe even sumproduct... never imagined simple nested ifs in array mode can do the magic... You are the master.

 

[Aladin Akyurek]

YOU SAVED MY DAY!!! BIG THANKS!! I only thought of using sumifs and maybe even sumproduct... never imagined simple nested ifs in array mode can do the magic... You are the master.

Glad to help.

 

[asalman07]

Hi Aladin,

You posted this on the 5th of Jan. Can you explain why that the 0 in your Index-Match formula below brings the Index bit piece of the formula to {5;2;1;5}. I would have never thought a zero would do that...

Thanks for your help.

Also...

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

 

[Qing Liu]

Here is another approach:

=SUM(B2:H4*(A2:A4=K1)*(YEAR(B1:H1)=YEAR(J1))*(MONTH(B1:H1)<=MONTH(J1)))
Array-enter the above formula.

@asal
In the Index-Match formula, the first 0 is for INDEX to return a column vector of the table, instead of a single cell reference, and the second 0 is for MATCH to return a exact-match.

 

[Aladin Akyurek]

Hi Aladin,

You posted this on the 5th of Jan. Can you explain why that the 0 in your Index-Match formula below brings the Index bit piece of the formula to {5;2;1;5}. I would have never thought a zero would do that...

Thanks for your help.

We can access various subareas of a given area (range) with INDEX which allows for reference construction.

The bit you refer to delivers all cells (rows) of the column range MATCH indicates:

INDEX(Range,0,N)

Also:

INDEX(Range,M,0)

delivers all cells (columns) of the row M indicates.

Thus, given B2:D5 below...

P	3	4
P	2	1
Q	0	6
R	1	1

<TBODY>
</TBODY>

a) second cell of column 2 obtains with:

=INDEX($B$2:$D$5,2,2)

This delvers:

2

b) first and second cells (rows) of column 2 obtain with:

=INDEX(B2:D5,N(CHOOSE(1,{1,2})),2)

Apply F9 in order to see that this delivers:

{3,2}

c) all cells of column 2, as noted above, obtain with:

=INDEX($B$2:$D$5,0,2)

Apply F9 in order to see that this delivers:

{3;2;0;1}

Hope this helps.

 

[asalman07]

We can access various subareas of a given area (range) with INDEX which allows for reference construction.

The bit you refer to delivers all cells (rows) of the column range MATCH indicates:

INDEX(Range,0,N)

Also:

INDEX(Range,M,0)

delivers all cells (columns) of the row M indicates.

Thus, given B2:D5 below...

P	3	4
P	2	1
Q	0	6
R	1	1

<tbody>
</tbody>

a) second cell of column 2 obtains with:

=INDEX($B$2:$D$5,2,2)

This delvers:

2

b) first and second cells (rows) of column 2 obtain with:

=INDEX(B2:D5,N(CHOOSE(1,{1,2})),2)

Apply F9 in order to see that this delivers:

{3,2}

c) all cells of column 2, as noted above, obtain with:

=INDEX($B$2:$D$5,0,2)

Apply F9 in order to see that this delivers:

{3;2;0;1}

Hope this helps.

Aladin,

Sorry for the delayed response.
Thank you so much for the beautifully laid out explanation.

 

[mikerickson]

This formula worked for me, but Aladin's question about the dates still stands.
=SUMPRODUCT(--(A2:A4="cat")*(B1:D1="January")*B2:D4)