Hi,
I have a formula that i found online....it is
=SUMPRODUCT(SUBTOTAL(9,OFFSET(AY5,ROW(AY5:AY5925)-ROW(AY5),0)),(AY5:AY5925>0)+0)
Basically it sums all positive visible numbers when i filter a column....the formula works perfectly but i want to understand the logic behind it..does anyone know how this works? my data starts on row AY5 and ends on AY5925...when i filter the a column then this formula adds all visible positive cells....i tried to break it down to understand it but it is not as simple....thanks
dbravo,
See if this helps...
Breaking down the formula:
'=SUMPRODUCT(SUBTOTAL(9,OFFSET(AY5,ROW(AY5:AY5925)-ROW(AY5),0)),(AY5:AY5925>0)+0)
=OFFSET(AY5,ROW(AY5:AY5925)-ROW(AY5),0) Evaluates to 0
=ROW(AY5:AY5925) Evaluates to 5
=-ROW(AY5) Evaluates to -5
=SUBTOTAL(9,OFFSET(AY5,ROW(AY5:AY5925)-ROW(AY5),0)) Evaluates to 0
The formula simplified becomes:
=SUMPRODUCT(0,(AY5:AY5925>0)+0)
This is the sum of '0' and all cells greater than 0 in the specified range
(AY5:AY5925>0)+0 This part of the SUMPRODUCT statement means: For each cell in the specified range greater than 0
The '+0' part makes each qualifying cell a number, not text
I copied the following from Excel Help…the '?' on the RH side of the top ribbon:
Syntax
SUBTOTAL(function_num,ref1,[ref2],...)
The SUBTOTAL function syntax has the following arguments:
Function_num**** Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal.
1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.
function_num = 9 is 'SUM'
Ref1**** Required. The first named range or reference for which you want the subtotal.
*************************
Syntax
OFFSET(reference, rows, cols, [height], [width])
Reference****Required. The reference from which you want to base the offset. Reference must refer to a cell or
range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
Rows****Required. The number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows
argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive
(which means below the starting reference) or negative (which means above the starting reference).
Cols****Required. The number of columns, to the left or right, that you want the upper-left cell of the result to refer to.
Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference.
Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
Perpa
