How to prepare summary by using formulas (NO PIVOT)

[ajaypal.sp]

Hi,


Pls find the attachment. in the attachment, i prepared the summary by using pivot. But i want to prepare that summary by using formulas only. bcoz i want to prepare automated report. Pls help.

Note: Sample data contains 6 months. but in future , my report contains 12 months and 'n' no. of different cities.

Thank you in advance.

Regards

 

[Nebu]

Hi:

Use Sumproduct formula!

Formulas on column K in the attached file.

Thanks

 

[p45cal]

bcoz i want to prepare automated report.

Why on earth can't you use pivot tables to create an automated report?!
Run this code in your sample file:

Sub blah()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Sample Data").Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=Sheets("SUMMARY").Range("F1"))  'change this to "A1" later.
  .ManualUpdate = True
  .ColumnGrand = False
  .RowGrand = False
  For Each pf In Array("Month", "Area", "Gender")
    With .PivotFields(pf)
      .Orientation = xlRowField
      .Subtotals(1) = True
      .Subtotals(1) = False
    End With
  Next pf
  .AddDataField .PivotFields("Sales"), "Total", xlSum
  .RowAxisLayout xlTabularRow
  .RepeatAllLabels xlRepeatLabels
  .ManualUpdate = False
  Set zzz = .TableRange2: x = zzz.Value: zzz.ClearContents: zzz.Value = x
End With
End Sub

It will cope with any number of cities (areas?).
If you want to keep the pivot table as a pivot, delete/comment-out the line beginning Set zzz =

 

[bosco_yip]

Hi,

If you can produce the Summary by Pivot Table, it is highly recommended. (please follow the above post #3 p45cal's describe)

Although formulas can do the same job, but it is complicated and needed to remove duplicate cells in multi-column by expensive formula.

Here's the file attached and formulas used for your understanding purpose.

1] In "Summary Sheet" Month column A2, array (CSE) formula copy down :

=IFERROR(INDEX('SAMPLE DATA'!B$2:B$3499,SMALL(IF(FREQUENCY(IF('SAMPLE DATA'!B$2:B$3499<>"",IF('SAMPLE DATA'!C$2:C$3499<>"",MATCH('SAMPLE DATA'!B$2:B$3499&'SAMPLE DATA'!$C$2:$C$3499&'SAMPLE DATA'!$D$2:$D$3499,'SAMPLE DATA'!B$2:B$3499&'SAMPLE DATA'!$C$2:$C$3499&'SAMPLE DATA'!$D$2:$D$3499,0))),ROW('SAMPLE DATA'!B$2:B$3499)-ROW('SAMPLE DATA'!B$2)+1),ROW('SAMPLE DATA'!B$2:B$3499)-ROW('SAMPLE DATA'!B$2)+1),ROWS(A$2:A2))),"")

2] In "Summary Sheet" Gender column B2, array (CSE) formula copy across Area column C2 and all copy down :

=IF($A2="","",INDEX('SAMPLE DATA'!C$2:C$3499,SMALL(IF(FREQUENCY(IF('SAMPLE DATA'!C$2:C$3499<>"",IF('SAMPLE DATA'!D$2:D$3499<>"",MATCH('SAMPLE DATA'!C$2:C$3499&'SAMPLE DATA'!$C$2:$C$3499&'SAMPLE DATA'!$D$2:$D$3499,'SAMPLE DATA'!C$2:C$3499&'SAMPLE DATA'!$C$2:$C$3499&'SAMPLE DATA'!$D$2:$D$3499,0))),ROW('SAMPLE DATA'!C$2:C$3499)-ROW('SAMPLE DATA'!C$2)+1),ROW('SAMPLE DATA'!C$2:C$3499)-ROW('SAMPLE DATA'!C$2)+1),COUNTIF($A$2:$A2,$A2))))

3] In "Summary Sheet" Total Sales column D2, formula copy down :

=IF($A2="","",SUMIFS('SAMPLE DATA'!E:E,'SAMPLE DATA'!B:B,A2,'SAMPLE DATA'!C:C,B2,'SAMPLE DATA'!D:D,C2))

p.s. array formula to be confirmed by pressing CTRL+SHIFT+ENTER 3 keystrokes altogether.

Regards
Bosco

 

[AVK]

Also you can try with below formula.
=SUMIFS('SAMPLE DATA'!$E:$E,'SAMPLE DATA'!$B:$B,$A2,'SAMPLE DATA'!$D:$D,$B2,'SAMPLE DATA'!$C:$C,$C2)