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

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,762
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
thank in advance.
.sst
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,628
Office Version
  1. 365
Platform
  1. Windows
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)
 

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,762
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
hi jason, thank but sorry i want the formula in a single cell not use helper column
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,628
Office Version
  1. 365
Platform
  1. Windows
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!
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,119,118
Messages
5,576,198
Members
412,706
Latest member
msousa25
Top