Re: Explanation of SUMPRODUCT

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 03/18/05 

Date: Fri, 18 Mar 2005 07:22:57 -0000

"Shawn O'Donnell" <ShawnODonnell@discussions.microsoft.com> wrote in message
news:AFF01FE0-C10C-4ECE-B54E-A51A61A2BA8B@microsoft.com...
> "Bob Phillips" wrote:
> > In the example that you show, which should be (at least)
> > =SUMPRODUCT(1/(COUNTIF(A1:A10,A1:A10)))
>
> In this version of the formula, I don't think you need SUMPRODUCT. SUM
will
> do, since you're dealing with a single array and you aren't doing any
> dot-producting. I don't even think you need to enter it as an array
formula.

Yes, we know this, but in this case SUM is an array formula SUMPRODUCT
isn't.

> But neither the SUMPRODUCT or SUM version will work if one of the cells is
> blank.
>
> For the purposes of exploring this SUMPRODUCT construct a little further,
> let's say we take Bill's example and erase the contents of the cell that
has
> "Jon" in it. The data in A1:A10 is then Bob,John,Bob,Bob,John,
> ,Bob,Bill,Bill,Max
>
> Here's a parsing of another popular version of this formula, one that
> ignores blank cells:
>
> =SUM((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Yes we know all this too, and it works just as well with SUMPRODUCT, which
is till not an array formula.As I recall, this technique was first suggested
with SP not SUM .

> Piece by piece, here's what that says:
>
> COUNTIF(A1:A10,A1:A10), as Bob explained, produces a 10-element array
where
> each element is the number of times the corresponding cell value appears
in
> the range A1:A10.
>
> The extra &"" at the end is a string concatenation. You're adding a
> zero-length string onto whatever was in each cell. That converts empty
> cells, which Excel interprets as the number 0, to empty strings. Why do
you
> want to do that? Because if you represent empty cells as 0, then you'll
end
> up with zero in the denominator of the 1/COUNTIF() expression. And you
know
> what happens when you do that. So the COUNTIF(A1:A10.A1:A10&"") part of
the
> expression evaluates to: {4;2;4;4;2;1;4;2;2;1}. If we didn't have the
&"",
> the array would have been {4;2;4;4;2;0;4;2;2;1}. Notice the zero.

You are not adding a "" onto whatever was ikn each cell, buut simply on to
the criteria values.

> Why is that zero there? Because COUNTIF promotes an empty cell to 0 if
the
> cell is in the 'criteria' argument, but not if the empty cell is in the
> 'range' argument position. Try this: put the number 0 in B1 and B2. Put
> COUNTIF(B1,B2) in cell B3. Then try deleting the zeros in B1 and B2 one
at a
> time. Watch how it affects B3. (Let me know if you find the
documentation
> on this.)

I assume tyhat you mean =COUNTIF(B1:B2,B2)

>snip

> I find that I can get away without using an array formula if I use
> SUMPRODUCT to add the array, but not if I use SUM. If I use SUM, I have
to
> enter the formula as an array formula. I suppose SUMPRODUCT knows how to
> handle an array divided by an array piecewise, while SUM doesn't.

It is not SUM that is the problem here but COUNTIF. SUM can work on arrays.
SUMPRODUCT can work on arrays. COUNTIF expects to work on a range array, but
a criteria value. To work on an array where a single value, be that a
hard-coded value or a celle reference, you have to use an array formula.

Relevant Pages

  • Re: adding cells within an array
    ... particular cell. ... SUM. ... I have an array with a series of zero and non-zero numbers. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Problem with using INDIRECT with SUMPRODUCT and ROW()
    ... help for SUMPRODUCT which is only about what it was first intended to do ... Biff - I was unaware that ROWreturned an array. ... I didn't just use SUM, because I actually want the sum of the squares of ... Since you say that you need to reference the current row, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Problem with using INDIRECT with SUMPRODUCT and ROW()
    ... SUMPRODUCT which is only about what it was first intended to do ... Biff - I was unaware that ROWreturned an array. ... I didn't just use SUM, because I actually want the sum of the squares of ... Since you say that you need to reference the current row, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Problem with using INDIRECT with SUMPRODUCT and ROW()
    ... Seems to me that MS *should* have support personnel that can solve/answer ... SUMPRODUCT which is only about what it was first intended to do ... Biff - I was unaware that ROWreturned an array. ... I didn't just use SUM, because I actually want the sum of the squares of ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Problem with using INDIRECT with SUMPRODUCT and ROW()
    ... SUMPRODUCT which is only about what it was first intended to do A1*B1+A2*B2 ... Biff - I was unaware that ROWreturned an array. ... I didn't just use SUM, because I actually want the sum of the squares of ... Since you say that you need to reference the current row, ...
    (microsoft.public.excel.worksheet.functions)