# SumIf or SumProduct?

#### aimee_quaife

##### New Member
Hello!

I'm working on a budget excel spreadsheet. In the workbook I have one spreadsheet called spending record, where I enter the purchases and another which has tables that summarize this info. In the summary table, I'm trying to do a formula that sums all the figures in column D if A=2011,B=Aug,C=Food, but I can't seem to get it to work

I've tried this so far...

=SUMPRODUCT(--(SPENDING RECORD’!A2:A300=2010),--(SPENDING RECORD’!B2:B300=”aug”),--(SPENDING RECORD’!C2:C300=”food”),--(--(SPENDING RECORD’!D2:D300))

=SUM(IF(‘SPENDING RECORD’!A2:A300=2010,IF(‘SPENDING RECORD’!B2:B300=”aug”),IF(‘SPENDING RECORD’!C2:C300="food",D2:D300)))

Any suggestions would be welcomed!!

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### xenou

##### MrExcel MVP
=SUMPRODUCT(--(SPENDING RECORD’!A2:A300=2010),--(SPENDING RECORD’!B2:B300=”aug”),--(SPENDING RECORD’!C2:C300=”food”),--(--(SPENDING RECORD’!D2:D300))

The above should work, but you must be careful whether upper case/lower case matters (it probably does). Also, the values in column B might be real dates formatted to show as month name, or may be text values. It's possible that even the values in A are text rather than numbers -- "2010" instead of 2010.

So it all comes down to knowing if you have text or numbers or dates ... and syntax accordingly. And beware of upper/lower casing.

#### Michael M

##### Well-known Member
try
Code:
``=SUMPRODUCT(--(A1:A1000=2011)*(B1:B1000="aug")*(C1:C1000="food")*(D1:D1000))``

##### MrExcel MVP
Hello!

I'm working on a budget excel spreadsheet. In the workbook I have one spreadsheet called spending record, where I enter the purchases and another which has tables that summarize this info. In the summary table, I'm trying to do a formula that sums all the figures in column D if A=2011,B=Aug,C=Food, but I can't seem to get it to work

I've tried this so far...

=SUMPRODUCT(--(SPENDING RECORD’!A2:A300=2010),--(SPENDING RECORD’!B2:B300=”aug”),--(SPENDING RECORD’!C2:C300=”food”),--(--(SPENDING RECORD’!D2:D300))

=SUM(IF(‘SPENDING RECORD’!A2:A300=2010,IF(‘SPENDING RECORD’!B2:B300=”aug”),IF(‘SPENDING RECORD’!C2:C300="food",D2:D300)))

Any suggestions would be welcomed!!

The quotes round the sheet names are incomplete.
In SumProduct, the range to sum usually does not need coercing (using the -- bit).
Check carefully whether you want 2000 or 2011.

Just enter:
Code:
``````=SUMPRODUCT(
--('SPENDING RECORD'!\$A\$2:\$A\$300=2011),
--('SPENDING RECORD'!\$B\$2:\$B\$300=”aug”),
--('SPENDING RECORD'!\$C\$2:\$C\$300=”food”),
'SPENDING RECORD'!\$D\$2:\$D\$300))``````

Control+shift+enter:
Code:
``````=SUM(
IF('SPENDING RECORD'!\$A\$2:\$A\$300=2011,
IF('SPENDING RECORD'!\$B\$2:\$B\$300=”aug”,
IF('SPENDING RECORD'!\$C\$2:\$C\$300=”food”,
'SPENDING RECORD'!\$D\$2:\$D\$300))))``````

#### aimee_quaife

##### New Member

Thanks for the suggestions. The sumif formula keeps coming up with 0 even though there are numbers to sum that meets the criteria. I've tried changing the words to have capitals, and treating the number like a date ("2010") but no luck.

The sumproduct keeps coming up with an error message and then highlights in internal (), but I don't know what that means. Or it will say it can find the worksheet spending record and get me to try and open a new file.

If I didn't show the previous formulas and you want to add all the all the food items(C:C) in a worksheet named Spending Record, but only if they were made in aug(B:B) 2010(A:A), AND this summary will be in a separate sheet, what would you do?

Thanks!!

#### T. Valko

##### Well-known Member
Thanks for the suggestions. The sumif formula keeps coming up with 0 even though there are numbers to sum that meets the criteria. I've tried changing the words to have capitals, and treating the number like a date ("2010") but no luck.

The sumproduct keeps coming up with an error message and then highlights in internal (), but I don't know what that means. Or it will say it can find the worksheet spending record and get me to try and open a new file.

If I didn't show the previous formulas and you want to add all the all the food items(C:C) in a worksheet named Spending Record, but only if they were made in aug(B:B) 2010(A:A), AND this summary will be in a separate sheet, what would you do?

Thanks!!
It sounds like you're describing a COUNT, not a sum.

What version of Excel are you using?

Does column B contain the month names as TEXT entries?

Does column A contain just the year numbers? These are not DATES, right?

What's in column C?

#### aimee_quaife

##### New Member

Thanks for your help... I definitely want a sum as I want to total the number of food purchases in column D. Columns A-C are all just formatted as general cells (not number, text or date). I do have them as lists though (I created drop down menus e.g. so I can select from food, car, household bills etc). This hasn't mattered in my past excel sheets where I've done similar formulas, but maybe that's something.

I'm working in Microsoft for Mac 2008 (12.2.8). All my other excel docs I created on a pc, so perhaps that's it? But I just checked my other excel docs and they're all working fine??

Sooo frustrating Obviously I'm doing something stupid!!

#### T. Valko

##### Well-known Member
Thanks for your help... I definitely want a sum as I want to total the number of food purchases in column D. Columns A-C are all just formatted as general cells (not number, text or date). I do have them as lists though (I created drop down menus e.g. so I can select from food, car, household bills etc). This hasn't mattered in my past excel sheets where I've done similar formulas, but maybe that's something.

I'm working in Microsoft for Mac 2008 (12.2.8). All my other excel docs I created on a pc, so perhaps that's it? But I just checked my other excel docs and they're all working fine??

Sooo frustrating Obviously I'm doing something stupid!!
Let's assume this is your data...

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:59px;" /><col style="width:61px;" /><col style="width:57px;" /><col style="width:67px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">Year</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Month</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Item</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Amount</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2010</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Jan</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">cake</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">18</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2010</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Feb</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">cake</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">64</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2011</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Mar</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">cake</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">87</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2011</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Jan</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">pie</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">87</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2010</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Jun</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">pie</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">70</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2010</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Aug</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">pie</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">55</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2010</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Aug</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">pie</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">95</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2011</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Aug</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">cake</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">54</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2010</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Oct</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">cake</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">22</td></tr></table> <br /><br />

To get the sum for pie in Aug 2010...

Use cells to hold the sum criteria:
• F2 = 2010
• G2 = Aug
• H2 = pie
Formula in I2:

=SUMPRODUCT(--(A2:A10=F2),--(B2:B10=G2),--(C2:C10=H2),D2:D10)

Result = 150

#### pliskers

##### Active Member
Have you tried putting a VALUE formula around your year criteria, keeping the quotation marks, i.e. VALUE("2010")

#### aimee_quaife

##### New Member
Thanks T-Valko! You got it working!! I don't know how I'd ever survive without MrExcel!!

Replies
1
Views
69
Replies
17
Views
191
Replies
29
Views
224
Replies
3
Views
315
Replies
1
Views
262