# Summarize with formula

#### Thomas Kuriakose

##### Active Member
Respected Sirs,

Kindly find attached a workbook with three tabs , "Data", "Pivot Summary" and "Formula Summary".

The data tab needs to be summarized as per the pivot summary and is working. There are some tables with shorter data with the same format.

Kindly guide and suggest if there is a formula method to arrive at the summary as shown in formula summary tab.

Thank you very much for your support always,

very much appreciated,

with regards,
thomas

#### Attachments

• 602.3 KB Views: 5

#### bosco_yip

##### Excel Ninja
Formula method

1] In F2, CSE formula copied across right to H2 and all copied down :

=IFERROR(INDEX(Data!A\$2:A\$25983,SMALL(IF(FREQUENCY(IF(Data!\$B\$2:\$B\$25983<>"",IF(Data!\$C\$2:\$C\$25983<>"",MATCH(Data!\$A\$2:\$A\$25983&Data!\$B\$2:\$B\$25983&Data!\$C\$2:\$C\$25983,Data!\$A\$2:\$A\$25983&Data!\$B\$2:\$B\$25983&Data!\$C\$2:\$C\$25983,0))),ROW(Data!\$A\$2:\$A\$25983)-ROW(Data!\$A\$2)+1),ROW(Data!\$A\$2:\$A\$25983)-ROW(Data!\$A\$2)+1),ROWS(A\$2:A2))),"")

2] In I2, copied down :

=SUMIFS(Data!D:D,Data!A:A,F2,Data!B:B,G2,Data!C:C,H2)

3] It is not recommended to use formulae for your "Data" sheet 26,000 lines x 3 columns data, it will cause re-calculation because of huge data (78,000 cells per each formula x 90 formula cells [F2:F31], totally equal to around 7 millions cells ) calculation and of course will slow down your computer performance. So, it is advise to use Pivot table, PQ or VBA.

Regards
Bosco

#### Attachments

• 606.3 KB Views: 6
Last edited:

#### Thomas Kuriakose

##### Active Member
Respected Sir,

Thank you so much for the formula method and it works perfectly.

Agreed, will not use the formula option.

Very much appreciated always,

with regards,
thomas