Sumifs problem

[markstro]

I have followed the setup wizard to the letter, I have used SUMIF extensively, never knew to try multiple criteria until now.
I get a return of zero, cannot find the error in the forumul.
=SUMIFS($M$2:$M$491,$B$2:$B$491,A491,$C$2:$C$491,J496)
There is data in column M that meets the criteria, I still get a return of zero. What am I missing?

 

[markstro]

I got it to work waiting for your answer.
Cell a491 I had formatted for yyyy, I then entered 1/1/2010 to set my 2010 criteria, that returned the zero for the SUMPRODUCT formula

when I entered just the numbers 2010, the cell returns to 1905 and the cell detail says 7/2/1905 and the SUMPRODUCT formula works.

What did I miss in my initial setup, something to do with the SUMPRODUCT itself.

 

[MrKowz]

Even though the cell may be formatted in a certain way, that does not change the value. So when you were testing against A491, you had 1/1/2010 in the cell. This means that it was looking for where the year in your date column was equal to "1/1/2010", not where the year was equal to "2010".

If you want to keep 1/1/2010 in the cell, then we have to test year of the date column vs the year of the test cell:

=SUMPRODUCT(--(YEAR($B$2:$B$491)=YEAR(A491)),--($C$2:$C$491=J496),$M$2:$M$491)
Otherwise, if you're fine with just putting in 2010, use the formula that is currently working for you.

 

[markstro]

Thanks so much for helping me out. I learned a lot on this problem.

 

[markstro]

One last question, how do I sum a range of cells vs. just a column.

I copied the formula and edited array 3 to a range vs. a column, it returns
#value.

 

[Haseeb Avarakkan]

Also, you can use your original SUMIFS like;

If you have date in A491 & formated as yyyy, then use;

=SUMIFS(M:M,B:B,">="&DATE(YEAR(A491),1,1),B:B,"<="&DATE(YEAR(A491),12,31),C:C,J496)

If you have just year in A491 use;

=SUMIFS(M:M,B:B,">="&DATE(A491,1,1),B:B,"<="&DATE(A491,12,31),C:C,J496)

 

[markstro]

I'll give it a try, any ideas on summing a range vs. one column???

 

[MrKowz]

In the third argument, you need to add together multiple 1-dimensional arrays of identical size:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">Acct</td><td style=";">kWh</td><td style=";">kWh(2)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1-Sep-09</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">20760</td><td style="text-align: right;;">19704</td><td style="text-align: right;;"></td><td style=";">Year</td><td style="text-align: right;;">2010</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1-Oct-09</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">18480</td><td style="text-align: right;;">18446</td><td style="text-align: right;;"></td><td style=";">Account #</td><td style="text-align: right;;">2109989060</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1-Nov-09</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">19220</td><td style="text-align: right;;">20584</td><td style="text-align: right;;"></td><td style=";">Sum</td><td style="text-align: right;;">389820</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1-Dec-09</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">19620</td><td style="text-align: right;;">12191</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1-Jan-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">16580</td><td style="text-align: right;;">18474</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1-Feb-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">17860</td><td style="text-align: right;;">14515</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">1-Mar-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">16780</td><td style="text-align: right;;">12402</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1-Apr-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">17140</td><td style="text-align: right;;">14926</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">1-May-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">18400</td><td style="text-align: right;;">19095</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1-Jun-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">12080</td><td style="text-align: right;;">19472</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">1-Jul-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">15860</td><td style="text-align: right;;">16294</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">1-Aug-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">14100</td><td style="text-align: right;;">13161</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">1-Sep-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">17180</td><td style="text-align: right;;">19815</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">1-Oct-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">16260</td><td style="text-align: right;;">14310</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">1-Nov-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">15200</td><td style="text-align: right;;">13665</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">1-Dec-10</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">15760</td><td style="text-align: right;;">20491</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">1-Jan-11</td><td style="text-align: right;;">2109989060</td><td style="text-align: right;;">12260</td><td style="text-align: right;;">19588</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet3</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">YEAR(<font color="Green">$B$2:$B$18</font>)=H2</font>),--(<font color="Red">$C$2:$C$18=H3</font>),D2:D18+E2:E18</font>)</td></tr></tbody></table></td></tr></table><br />

 

[markstro]

Thanks again, can't get to that until Monday, I will let you know how it goes though.