Too many 'if's!

[Guest]

Column A has a list of items, B has their cost. Column C states if the item
is to be purchased, and D states from which account.

So I need a formula that will add up the costs from column A only if that
item is to be purchased under a given account.

So I need Excel to add values from B4:B21 if C4:C21=Yes AND D421="Petty
Cash" for each row.

It's frustrating, it feels like I'm on the brink of having it, but I'm just
not seeing something somewhere.

 

[papou]

Hello Ben
=SUMPRODUCT((C4:C21="Yes")*(D421="Petty Cash")*(B4:B21))

HTH
Cordially
Pascal

 

[Guest]

Yes! That's exactly what I needed to accomplish! I never considered that
because I saw 'product' in the term and assumed it had something to do with
multiplying, not adding. Not going to pretend to understand why this works,
but it does and I'm happy. Thanks a bunch!

 

[papou]

Hi Ben
Thanks for your feedback.
The wildcard * in the SUMPRODUCT function has a different use from the
traditional multiplication.
You may find some interesting information here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cordially
Pascal

 

[Guest]

You were correct in your assumption that PRODUCT had something to do with
multiplication, SUMPRODUCT actually means "the SUM of the PRODUCTS". Usually
this is done for a matrix of values.

But when used in this manner it works by looking at each row individually
and returning the SUM of the PRODUCT, but the PRODUCT is made up of values
from one row. The logical tests return either -1 (true) or 0 (false). So
any false statement will cause the result of the multiplication to be zero.
Otherwise the result of -1 * -1 = 1 and then multiplied by the amount = the
amount.

You may see similar use of SUMPRODUCT written as:
SUMPRODUCT(--(C4:C21="Yes"),--(D421="Petty Cash"),--(B4:B21))
where the double-negative 'coerces' the TRUE/FALSE results to a numeric
value and the comma (,) acts as the * operator since parameters within a
SUMPRODUCT() are multiplied together.