Sumproduct help

anwarawna

New member
Joined
May 8, 2013
Messages
3
Reaction score
0
Points
0
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))
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

anwarawna

New member
Joined
May 8, 2013
Messages
3
Reaction score
0
Points
0
Thanks NBVC, Maybe this would help in your Explanation..Cashbook balance is the reference point.

A
Transaction DateTypeCustomer/SupplierReferenceDescriptionInclusive AmountTax CodeBank CodeAccount NumberStatement DateCashbook Balance
2/28/2013DOpeningO/BOpening Bank Statement Balance3,400.00EB1BS-3992/28/20133,400.00-
2/28/2013DOpeningO/BOpening Bank Statement Balance1,600.00EB2BS-3992/28/20131,600.00
2/28/2013DOpeningO/BOpening Bank Statement Balance-EB3BS-3992/28/2013-
2/28/2013DOpeningO/BOpening Petty Cash Balance1,000.00EPCBS-3992/28/20131,000.00
3/2/2013WXY SolutionsInvoice EXP09Internet Service Provider3234.70
AB1IS-3803/2/20133,234.70
3/5/2013WHollardDebit OrderInsurance2869.90AB1IS-3403/5/20132,869.90
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,941
Reaction score
0
Points
36
Excel Version(s)
O365
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:

anwarawna

New member
Joined
May 8, 2013
Messages
3
Reaction score
0
Points
0
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?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Possibly trying to get around any possible rounding issues? (Maybe time is included, but hidden in column A or BalDate)
 
Top