SumIf or SumProduct?

aimee_quaife

New Member
Joined
Sep 20, 2010
Messages
8
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 :confused:

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

Some videos you may like

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
Joined
Mar 2, 2007
Messages
16,688
Office Version
  1. 2013
Platform
  1. Windows
=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
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
try
Code:
=SUMPRODUCT(--(A1:A1000=2011)*(B1:B1000="aug")*(C1:C1000="food")*(D1:D1000))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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 :confused:

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
Joined
Sep 20, 2010
Messages
8

ADVERTISEMENT

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
Joined
May 9, 2009
Messages
16,623
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
Joined
Sep 20, 2010
Messages
8

ADVERTISEMENT

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 :mad: Obviously I'm doing something stupid!!
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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 :mad: 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
Joined
Sep 26, 2002
Messages
394
Office Version
  1. 2016
Platform
  1. Windows
Have you tried putting a VALUE formula around your year criteria, keeping the quotation marks, i.e. VALUE("2010")
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,987
Members
409,614
Latest member
wile2u

This Week's Hot Topics

Top