# Sumproduct , Abs, ..Calculate Absolut Number only Visible Cell Not Use Array Function

##### Well-known Member
hi all..
how to make formula contains Sumproduct,Abs, Subtotal..
i want to summing absolut number using Sumproduct but in visible cell.
here layout
Book1
IJ
2data
4-3
64hiden row
75
88sum absolut
Sheet1

i try =sumproduct(abs(subtotal(109,.....) not work
note ;
don't use array function
.sst

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### jasonb75

##### Well-known Member
don't use array function
Sumproduct is an array function, you will not be able to do it without either a volatile array or a helper column.
Not hidden
Book1
IJ
2data
30
4-33
50
644
755
8812
Sheet4
Cell Formulas
RangeFormula
J3:J7J3=ABS(SUBTOTAL(109,I3))
J8J8=SUM(J3:J7)

Hidden
Book1
IJ
2data
4-33
755
888
Sheet4
Cell Formulas
RangeFormula
J4,J7J4=ABS(SUBTOTAL(109,I4))
J8J8=SUM(J3:J7)

##### Well-known Member
hi jason, thank but sorry i want the formula in a single cell not use helper column

#### jasonb75

##### Well-known Member
I only suggested a helper because you rejected the use of an array formula in your first post.
Excel Formula:
``=SUMPRODUCT(ABS(SUBTOTAL(109,OFFSET(\$I\$3,ROW(\$I\$3:\$I\$7)-ROW(\$I\$3),0))))``
Sumproduct doesn't always need to be array confirmed with ctrl shift enter, but it is still an array!

##### Well-known Member
hi Jason, thank you, work great!!!!

Replies
1
Views
381
Replies
3
Views
528
Replies
0
Views
120
Replies
1
Views
215
Replies
5
Views
259