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

 

[Aladin Akyurek]

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

Just for the record...

SumProduct formula has a superfluous paren at the end.
The formula with Sum and If requires control+shift+enter.

 

[T. Valko]

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

You're welcome. Thanks for the feedback!

P.S.

I don't know anything about Apples, but in Excel 2007 for PCs there is a function called SUMIFS. This is a more efficient, although less versatile, version of SUMPRODUCT.

If your version of MAC Excel supports the SUMIFS function then that would be a better choice over SUMPRODUCT.

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

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

 

[pliskers]

Can someone explain, please, under which circumstances you separate the conditions by commas, and which by multiplication signs (asterisks)?

 

[T. Valko]

Can someone explain, please, under which circumstances you separate the conditions by commas, and which by multiplication signs (asterisks)?

See this...

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Aladin Akyurek]

Can someone explain, please, under which circumstances you separate the conditions by commas, and which by multiplication signs (asterisks)?

SumProduct's native syntax require commas between terms which are in turn are expected to be numbers like in

SUMPRODUCT(A2:A10,B2:B10)

with A2:A10 and B2:B10 both housing numbers.

When the terms are conditional expressions like

A2:A10="X" and B2:B10="Q"

they need to be coerced (transformed) into numbers. Since such expressions evaluate to arrays of TRUE and FALSE values, they can be converted to their numeric equivalents, that's, 1's and 0's.

--(A2:A10="X") and --(B2:B10="Q")

is one way of obtaining the required coercion, that is, using double negation. So, we get:

SUMPRODUCT(--(A2:A10="X"),--(B2:B10="Q"))

Another way to obtain the required coercion from TRUE/FALSE to 1/0 is
to use an arithmetic operator like multiplication. This gives:

SUMPRODUCT((A2:A10="X")*(B2:B10="Q"))

Note. The latter is very much like a SUM formula requiring control+shift+enter...

SUM((A2:A10="X")*(B2:B10="Q"))

which has possibly inspired its SumProduct equivalent.

 

[pliskers]

Very clear explanation, thank you so much!

 

[Aladin Akyurek]

Very clear explanation, thank you so much!

You are welcome. Thanks for the feedback.