SumIf or SumProduct?

[aimee_quaife]

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

 

[xenou]

=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]

try

=SUMPRODUCT(--(A1:A1000=2011)*(B1:B1000="aug")*(C1:C1000="food")*(D1:D1000))

 

[Aladin Akyurek]

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:

=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:

=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]

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]

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]

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]

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

Book1
	A	B	C	D
1	Year	Month	Item	Amount
2	2010	Jan	cake	18
3	2010	Feb	cake	64
4	2011	Mar	cake	87
5	2011	Jan	pie	87
6	2010	Jun	pie	70
7	2010	Aug	pie	55
8	2010	Aug	pie	95
9	2011	Aug	cake	54
10	2010	Oct	cake	22
Sheet1

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]

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

 

[aimee_quaife]

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