sumproduct logic

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: mark1 (anonymous_at_discussions.microsoft.com)
Date: 05/06/04 

Date: Thu, 6 May 2004 11:07:59 -0700

I have this formula, entered as an array:

=SUMPRODUCT((A9:A7250)*(IF((B9:B7250>H9)+(C9:C7250>H9)+
(D9:D7250>H9)+(E9:E7250>H9)+(F9:F7250>H9),1,0)))

I understand that the + sign acts as "OR", but even though
the formula works, I can't quite get the logic. To me it
evaluates each column and assigns either a one or zero to
each cell within that column. Then the + sign adds the
corresponding cells from each column up. So you may have
values that are greater than 1. However, I don't know how
the formula moves from there. How does the IF know
whether to give it a one or zero? I mean I know it's
looking at each value and if it's greater than 0, it
assigns it a value of 1. But how does it know that I want
values greater than zero? Where is that in the 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: Using COUNTIF to search for existence
    ... between the two of them not having zero as part of the picture this time. ... Using the same array formula ... You get a FALSE because there is no duplicate. ... So you can see that the count for a totally Blank cell is the same as ...
    (microsoft.public.excel.worksheet.functions)
  • Re: MODE() is there a way to return bi-modal or multimodal results
    ... column L beginning in cell L1, ... L2 [array formula]: ... That formula doesn't like zeroes. ... formula returns zero when zero is a mode. ...
    (microsoft.public.excel.worksheet.functions)
  • RE: hlookup problem
    ... Can't do that because there is other information in the array that is ... need it to lookup the last value using the entire array. ... but since all dates appear it still returns a zero. ... In one case the cell value is actually 0 and in another case the cell ...
    (microsoft.public.excel.misc)
  • Re: Highest n consecutive values
    ... Where cell C1 contains the number of cells you want aggregated and tested. ... both formulas work either as regular or as array ... drive this array with a multiplier, ... might be zero and, most importantly, where if the multiplier is zero, ...
    (microsoft.public.excel)