Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

sumproduct

ResolvedVersion 2010

Raani has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

SUMPRODUCT --

Hi,

Could you tell me what the SUMPRODUCT and double minus function does please?

Many thanks

RE: SUMPRODUCT --

Hi Raani,

Thank you for your question and welcome to the forum.

SUMPRODUCT function does is multiply the elements of one or more arrays(ranges of cells) and then add or sum the products together.

=sumproduct(a1:a8,b1:b8) This would multiply each row and then total all the row results to give a grand total.

=SUMPRODUCT(--(FREQUENCY(B2:B8,B2:B8)>0))

The formula above results in the number of unique values in a list.

When you introduce a condition as one of the arguments, it tests the condition against each element or cell in the array. This results in Boolean values so true or false.

Now the equivalent to True and False is 1 and 0. In order to count the number of unique values, we need to replace true and false with 1 and 0. So the -- converts the True and False to 0 and 1 so we can then count the unique values.

The Frequency function just gives you how many times each item appears in the list.

I hope this answers your questions.

Regards

Simon

Fri 31 May 2013: Automatically marked as resolved.

Excel tip:

Selecting your working range

In excel if you have an area you want to highlight, press Ctrl + * or Ctrl+Shift+8 (to get the *). This will select your working range.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.