Program for creating multi-level subtotal() formulas in MS Excel compatible tables

Program name: efg_subtotal_www.mercator.si.exe

More information: www.indexadd.com

Sponsored by: www.mercator.si

Before using formulas, table must be sort on group of columns.

Column name for start sort = b

The number of columns in the group for sort = 3

Columns must be arranged in a compact group.

Column name with data (subtotal) = e

Row number (start) with data cell = 3

Column name with start of free area = g

--------------------

step = 1

=IF(B3<>B4,ROW(),"")

Copy formula in cell G3.

Then copy formula from these cell by the end of the table.

--------------------

step = 2

=IF(OR(C3<>C4,G3<>""),ROW(),"")

Copy formula in cell H3.

Then copy formula from these cell by the end of the table.

--------------------

step = 3

=IF(OR(D3<>D4,G3<>"",H3<>""),ROW(),"")

Copy formula in cell I3.

Then copy formula from these cell by the end of the table.

--------------------

step = 4

=IF(G3<>"",T("SUBTOTAL_01(B="&B3&", E)="),"")

Copy formula in cell J3.

Then copy formula from these cell by the end of the table.

--------------------

step = 5

=IF(G3<>"",SUM(INDIRECT("E"&TEXT((MAX(G$3:INDIRECT("G"&TEXT(ROW()-1,"#")))+IF(ROW()>3,1,0)),"#")):INDIRECT("E"&TEXT(ROW(),"#"))),"")

Copy formula in cell K3.

Then copy formula from these cell by the end of the table.

--------------------

step = 6

=IF(H3<>"",T("SUBTOTAL_02(B="&B3&", C="&C3&", E)="),"")

Copy formula in cell L3.

Then copy formula from these cell by the end of the table.

--------------------

step = 7

=IF(H3<>"",SUM(INDIRECT("E"&TEXT((MAX(H$3:INDIRECT("H"&TEXT(ROW()-1,"#")))+IF(ROW()>3,1,0)),"#")):INDIRECT("E"&TEXT(ROW(),"#"))),"")

Copy formula in cell M3.

Then copy formula from these cell by the end of the table.

--------------------

step = 8

=IF(I3<>"",T("SUBTOTAL_03(B="&B3&", C="&C3&", D="&D3&", E)="),"")

Copy formula in cell N3.

Then copy formula from these cell by the end of the table.

--------------------

step = 9

=IF(I3<>"",SUM(INDIRECT("E"&TEXT((MAX(I$3:INDIRECT("I"&TEXT(ROW()-1,"#")))+IF(ROW()>3,1,0)),"#")):INDIRECT("E"&TEXT(ROW(),"#"))),"")

Copy formula in cell O3.

Then copy formula from these cell by the end of the table.