Results 1 to 6 of 6

Thread: Sumproduct help

  1. #1

    Sumproduct help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guys,
    First time here,came across the gigantic function below.Care some1 make me understand please?

    =SUMPRODUCT((BalDate<=($A5+(ROW($A5)/86400)))*(Type="D")*(BankCode=$H5)*(Amount))-SUMPRODUCT((BalDate<=($A5+(ROW($A5)/86400)))*(Type="W")*(BankCode=$H5)*(Amount))

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Read this article to better understand the many uses of SUMPRODUCT... http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    In short, in this case, you are comparing three named ranges to specific criteria. When you compare this way, you end up with arrays of TRUE and FALSE results. The arrays are then multiplied against each other yielding a final array of 1's and 0's (TRUE*TRUE=1, TRUE*FALSE=0, FALSE*FALSE=0, FALSE*TRUE=0). This array is then multiplied by the respectively positioned values in the sum array (Amount). Then that final result is summed to get a single value (sum of all Amount values where all 3 conditions checked were TRUE in the same rows).

    In the above formula, you are doing 2 separate SUMPRODUCT functions, and subtracting the result of one from the other to get a final number.


  3. #3
    Thanks NBVC, Maybe this would help in your Explanation..Cashbook balance is the reference point.

    A
    Transaction Date Type Customer/Supplier Reference Description Inclusive Amount Tax Code Bank Code Account Number Statement Date Cashbook Balance
    2/28/2013 D Opening O/B Opening Bank Statement Balance 3,400.00 E B1 BS-399 2/28/2013 3,400.00 -
    2/28/2013 D Opening O/B Opening Bank Statement Balance 1,600.00 E B2 BS-399 2/28/2013 1,600.00
    2/28/2013 D Opening O/B Opening Bank Statement Balance - E B3 BS-399 2/28/2013 -
    2/28/2013 D Opening O/B Opening Petty Cash Balance 1,000.00 E PC BS-399 2/28/2013 1,000.00
    3/2/2013 W XY Solutions Invoice EXP09 Internet Service Provider 3234.70 A B1 IS-380 3/2/2013 3,234.70
    3/5/2013 W Hollard Debit Order Insurance 2869.90 A B1 IS-340 3/5/2013 2,869.90

  4. #4
    Most of it is very simple, it is looking for Bank Types of D and W and a Bank Code as in H5(H6,...), but there is one confusing part to me. Why is it comparing the date against a date value in A5(A6,...) but is adding 1 sec per row. Why?
    Last edited by Bob Phillips; 2014-03-10 at 07:23 PM.

  5. #5
    Quote Originally Posted by Bob Phillips View Post
    Most of it is very simple, it is looking for Bank Types of D and W and a Bank Code as in H5(H6,...), but there is one confusing part to me. Why is it comparing the date against a date value in A%(A6,...) but is adding 1 sec per row. Why?
    Bob, Thats the part that confuses me too..i really have no answer to why?

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Possibly trying to get around any possible rounding issues? (Maybe time is included, but hidden in column A or BalDate)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •