Sum function with asterisks

[DianeG]

I have been given this function and understand what its doing but don't
understand what the asterisks are for, is it for multiplying or are they
wildcards?

=SUM((A2:A10="Phone")*(B2:B10="Jones")*C2:C10)

I've never seen them used like this before. Help please!

Diane

 

[Elkar]

They are being used for multiplication.

The (A2:A10="Phone") and (B2:B10="Jones") both return either TRUE or FALSE.
Excel assigns the value of 1 to TRUE and 0 to FALSE. So, basically, the
value in column C is being multiplied by either 1 or 0 for each value in the
range.

HTH,
Elkar

 

[Pete_UK]

The terms (A2:A10="Phone") and (B2:B10="Jones") are logical
expressions which will equate to True or False - when used in
arithmetic they will take on the values 1 and 0 respectively.

So, you can think of the asterisks are multiplying the values as Excel
steps through the arrays A2:A10, B2:B10 and C2:C10 - the first 2 terms
will only be 1 or 0, so the combined value will only be non-zero if
the A and B element is non-zero, and C is non-zero. Thus the function
will add the values of the C range only when the A cell contains
"Phone" AND the B cell contains "Jones".

You can also think of the asterisks as carrying out an AND operation
on two or more arrays.

Hope this helps.

Pete

 

[Gord Dibben]

Diane

I think you should have been given the formula

=SUMPRODUCT((A2:A10="Phone")*(B2:B10="Jones")*C2:C10)

which sums the values in C2:C10 where A has Phone and B has Jones

See Bob Phillips' site for all you need to know about SUMPRODUCT and the
asterisks which in this case are multipliers.

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


Gord Dibben MS Excel MVP