• Dennis,


    I am intrigued by the formula you posted above (which seems to work very well, BTW). It seems to incorporate criteria in a way that I have not seen before (e.g. (A1:A5="01")). I did not know that it was possible to do that where you would normally just specify a range or array.


    Where else can this be used and what limitations are there?


    DuckBill


  • looking at the first part of the sumproduct solution : A1:A5="01"


    A1:A5="01" is boolean - ie the statement is either TRUE or FALSE for each : in this case :


    TRUE
    TRUE
    TRUE
    FALSE
    FALSE


    or, since TRUE=1 and FALSE=0 :


    1
    1
    1
    0
    0


    looking at the second part :


    B1:B5<1


    this, again, is boolean - the statement is either TRUE or FALSE : in this case :


    TRUE
    TRUE
    FALSE
    TRUE
    FALSE


    and again, since TRUE=1 and FALSE=0 :


    1
    1
    0
    1
    0


    since there are two conditions, we take the products of both criteria in this array


    1x1
    1x1
    1x0
    0x1
    0x0


    equals


    1
    1
    0
    0
    0


    then we are summing their results :


    1+1+0+0+0 = 2


    so the net result is 2


    2 conditions conform to the criteria we specified


    this is how we bring back a "count", utilising the boolean logic of sumproduct


    once you get your head round it, give us a shout and you'll see how we also use it to bring back a SUM of that count, so rather have it bring back 2, it can bring back the total values of those 2 - it's very flexible....


    Dennis - I'm absolutely no expert on this, so any comments / additions are welcomed :)

  • caveat : (!)


    once you discover SUMPRODUCT, it seems like a gift from heaven


    however imagine having 10 criteria (ie names that begin with "B", in the "North", where the value is less than $5000, with a date in January, for salesman "Bloggs", for company "ABC Corp", etc etc etc" and you're interrogating 5,000 records....


    the sumproduct will interrogate each record (all 5000 of them) ten seperate times, to ascertain whether each criteria is in fact TRUE or FALSE.... then when it's got all these, it multiplies them all together 5,000 x 10 calculations, then adds up the results....


    with such a query, I wouldn't be surprised if it took a minute or so to calculate


    so while it's useful, if you beging to notice performance degredations on your worksheets as you use them more often, you may be better off looking at the range of Database formulae (in the functions sections, but all preceeded with the letter D.... DSUM, DGET, DCOUNTA etc etc) which basically do the same thing but are much easier to set up and maintain, and, from experience, are a lot quicker

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!