• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

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
 
Top