Subtotal formula Subtotals greater than zero needed.

[Fin Fang Foom]

Is there a Subtotal(9, formula that it will only Subtotal greater than zero?
I tried this but does'nt work.

Any Ideas?

=IF(SUBTOTAL(9,S3:S86)>0,SUBTOTAL(9,S3:S86),0)

 

[Fin Fang Foom]

I'm sorry it works. Thank You so much.



Also I have this code that it will insert the module into the workbook.
But the problem is that when I run the code it will open the folder then I have to find the file in my C\drive and when I choose the file then it will insert the module to the workbook. Can the code be modified that I could just run the maco and it will insert the module automatically.

   Dim wbFileName As Variant
Dim FS As Office.FileSearch
Dim vaFileName As Variant
wbFileName = Application.GetOpenFilename("XLS Files (*.xls), *.xls")
If wbFileName <> False Then
Workbooks.Open Filename:=wbFileName
Set FS = Application.FileSearch
With FS
'clear old search criteria
.NewSearch
'directory to search
.LookIn = "C:"
'look for .bas (Module) files
.Filename = "Inventory.bas"
'carry out search
.Execute
For Each vaFileName In .FoundFiles
'MsgBox "About to import module " & vaFileName
ActiveWorkbook.VBProject.VBComponents.Import Filename:=vaFileName
Next vaFileName

 

[Aladin Akyurek]

=SUMPRODUCT(1-SUBTOTAL(3,OFFSET(U4:U23,ROW(U4:U23)-ROW(U4),0,1)),--(U4:U23>0),U4:U23)

 

[Fin Fang Foom]

Thank You so much Aladdin,

Did it took awhile to figuer this out?

Can you explain me the functions in the formula that how it works?

 

[Aladin Akyurek]

Thank You so much Aladdin,

You're welcome.

Did it took awhile to figuer this out?

It takes some time the obvious to pop up.

Can you explain me the functions in the formula that how it works?

In

=SUMPRODUCT(1-SUBTOTAL(3,OFFSET(U4:U23,ROW(U4:U23)-ROW(U4),0,1)),--(U4:U23>0),U4:U23)

Longre's SUBTOTAL idiom assign 1 to all visible cells in the range of interest.

SUBTOTAL ignores cells housing SUBTOTOTAL formulas: They all get 0's.

Thus, given your sample, the first conditional,

1-SUBTOTAL(3,OFFSET(U4:U23,ROW(U4:U23)-ROW(U4),0,1))

evaluates to:

1-{1;1;1;1;0;0;1;0;0;1;1;1;0;0;1;1;0;0;1;0}

===>

{0;0;0;0;1;1;0;1;1;0;0;0;1;1;0;0;1;1;0;1}

The second conditional

--(U4:U23>0)

to:

{0;0;0;1;0;0;0;0;0;1;1;1;1;0;1;0;1;0;0;0}

And the range to sum,

U4:U23

to:

{-1.2;-0.18;-0.17;0.29;-1.26;0;-2.33;-2.33;0;0.23;1.69;4.01;5.93;0;0.5;0;0.5;0;-3.06;-3.06}

SumProduct multiplies the evaluations, which becomes:

{0;0;0;0;0;0;0;0;0;0;0;0;5.93;0;0;0;0.5;0;0;0}

a result it then sums.