Troubleshoot SumIf

[typewriterguy]

Need help with troubleshooting sumif, using Excel 2003

In the Total column, I want to sum Jan to Mar only if the Fruit is equal to Oranges; so desired value is 15.

Using "=SumIf(A3,"Oranges",C3:E3) returns a value of 5 (only January).

What's the correct formula syntax so that I get a value of 15, only for oranges - not for apples? thanks.

<TABLE style="WIDTH: 168pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=224 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" span=3 width=40><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Fruit</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 30pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=40>Total</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 30pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=40>Jan</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 30pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=40>Feb</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 30pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=40>Mar</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Apples</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=SUMIF(A2,"capex",C2:E2)' x:num>0</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Oranges</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=SUMIF(A3,"Oranges",C3:E3)' x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD></TR></TBODY></TABLE>

 

[barry houdini]

SUMIF requires a one-to-one relationship between criteria and sum ranges, i.e. they need to be the same size. If you don't explicitly make them the same size then Excel does that implicitly for you, your formula is the equivalent of

=SUMIF(A3:A4,"Oranges",C3:C4)

for what you require you can use SUMPRODUCT, i.e.

=SUMPRODUCT((A3:A4=Oranges")*C3:E4)

or an "array formula"

=SUM(IF(A3:A4=Oranges",C3:E4))

which needs to be confirmed with CTRL+SHIFT+ENTER