SumProduct Question

MyExcel

MyExcel

Well-known Member
Joined
Sep 25, 2008
Messages
508
hi
when we use * in SumProduct
and
when we use , in sumproduct
i see this formula
=SUMPRODUCT(ISNUMBER(C2:C9)*(B2:B9))
and it is give me right answer
but when i change
=SUMPRODUCT(ISNUMBER(C2:C9),(B2:B9))
it is give me zero
===========
????
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
when you use * the boolean values are converted to 0 or 1 by excel, but you need to convert them manually by using double unary operator when you use ','

=SUMPRODUCT(--(ISNUMBER(C2:C9)),(B2:B9))
 
Upvote 0
ok
so why we use , instead of * in some formula's
u told * convert Boolean to 1's and 0's and it is coerced to numeric value
so that is mean it is better to use * always not ,
 
Upvote 0
I guess the page which GTO provided - would give you a fair idea on how SUMPRODUCT() works in Excel.

Moreover, just to add - an asterisk (*) is also used in replacement of the AND operator for array formulas just like (+) for the OR operator.
 
Upvote 0
Stormseed said:
Moreover, just to add - an asterisk (*) is also used in replacement of the AND operator for array formulas just like (+) for the OR operator.
Click to expand...

yes i am reading now the link which GTo give me

* = and
+ = or
just in array formula
 
Upvote 0
MyExcel said:
hi
when we use * in SumProduct
and
when we use , in sumproduct
i see this formula
=SUMPRODUCT(ISNUMBER(C2:C9)*(B2:B9))
and it is give me right answer
but when i change
=SUMPRODUCT(ISNUMBER(C2:C9),(B2:B9))
it is give me zero
===========
????
Click to expand...

With the comma syntax, you need to coerce logical results of TRUE and FALSE to their numeric equivalents of 1 and 0 like in:

=SUMPRODUCT(--ISNUMBER(C2:C9),B2:B9)

=SUMPRODUCT(ISNUMBER(C2:C9)+0,B2:B9)

where the IsNumber test yields TRUE's and FALSE's.

When you use the * syntax as in:

=SUMPRODUCT(ISNUMBER(C2:C9)*(B2:B9))

the math operator takes care of coercion as in:

=TRUE*5 ==> 5
=FALSE*9 ===> 0

The advantage of the comma syntax is that it's the native syntax and it allows for text values in the range to sum while the * syntax doesn't.

Moreover, the comma syntax requires that the terms must have the same shape: Matrix,Matrix,Matrix,... or Vector,Vector,Vector,... Not mix up matrices and vectors together.
 
Upvote 0
thanx Aladin Akyurek
it is really great information
but can u give me small example to understand ur note here
The advantage of the comma syntax is that it's the native syntax and it allows for text values in the range to sum while the * syntax doesn't
Click to expand...
 
Upvote 0
MyExcel said:
thanx Aladin Akyurek
it is really great information
but can u give me small example to understand ur note here
Click to expand...

A2: a
A3: b
A4: a

B2: x
B3: x
B4: x

C2: =3*PI()
C3: =""
C4: =0.2*LOG(10)

E2: a
F2: x

G2:

=SUMPRODUCT(--($A$2:$A$4=E2),--($B$2:$B$4=F2),($C$2:$C$4))

H2:

=SUMPRODUCT(($A$2:$A$4=E2)*($B$2:$B$4=F2)*($C$2:$C$4))

I2:

=SUMPRODUCT(($A$2:$A$4=E2)*($B$2:$B$4=F2),($C$2:$C$4))

I2 will behave G2. The difference is that G2 is expected to perform a tad faster.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Excel chart with a line that will adjust certain cells
Replies
0
Views
134
jondavis1987
J
B
  • Question
Excluding a cell from MINIFS calculation - error when excluded cell is equal to first cell in range
Replies
2
Views
318
FormR
FormR
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
868
felixstraube
F
Y
  • Solved
Variable decimal format: displayed value differs from the value used when referenced in another formula
Replies
4
Views
380
Peter_SSs
Peter_SSs
J
  • Question
Sumproduct with Partial Text
2
Replies
15
Views
365
Alex Blakenburg
A

Forum statistics

Threads
1,216,462
Messages
6,130,781
Members
449,591
Latest member
sharmavishnu413

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top