Combining Subtotal and Sumif

[saeed]

A very happy new Year to you all ( in case this IS your New Year!).​

I'd appreciate your help with the following problem:
I've got a table similar to the attached file.
I need to add the product sales for each product AND month.
I can use Subtotal to add up sales for each month; but how do I loop thru the products?
It must add up the sales for product "A" in "Jan", then in "Feb" etc.
Thanks

 

[SirJB7]

Hi, saeed!

Give a look at the uploaded file. It uses a helper column E, and the results are in columns F:H, as follows:

E2: =SI(CONTAR.SI.CONJUNTO(A$1:A2;A2;B$1:B2;B2)=1;MAX(E$1:E1)+1;0) -----> in english: =IF(COUNTIFS(A$1:A2,A2,B$1:B2,B2)=1,MAX(E$1:E1)+1,0)
F2: =SI.ERROR(INDICE(A:A;COINCIDIR(FILA()-1;$E:$E;0));"") -----> in english: =IFERROR(INDEX(A:A,MATCH(ROW()-1,$E:$E,0)),"")
H2: =SI(F2="";"";SUMAR.SI.CONJUNTO(C:C;A:A;F2;B:B;G2)) -----> in english: =IF(F2="","",SUMIFS(C:C,A:A,F2,B:B,G2))

Copy across F2 thru G2; copy down E2:H2 as required.

Regards!

 

[Faseeh]

Hi Saeed,

Assalam O alaikum,

You can simply create a pivot table! See attached file.

 

[saeed]

Thanks folks! This is what I was I looking for.

 

[saeed]

Another related question:
How could it done in VBA?
How can I loop thru the data? I mean, how can I detect the change in the Month columns from for example Jan to Feb? And in Product column from A to B?
All I can think of, would be comparing the value in a cell to the previous cell and if different then check the value in Product Column; if the same add the value in Sales column.
Surely, there's a better, more elegant method?

 

[Debraj]

Marhaban saeed,

This is what I was I looking for

and

How could it done in VBA?

by combining this two line.. you cna go for create pivot table using VBA
try this..

Sub CombiningSubtotalandSumif()
  Range("A1").CurrentRegion.Select
  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
  Sheets("Tabelle1").UsedRange).CreatePivotTable TableDestination:="", TableName:= _
  "nEWpIVOTtABLE", DefaultVersion:=xlPivotTableVersion10
  ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)
  'ActiveSheet.PivotTables("nEWpIVOTtABLE").AddFields RowFields:="Name"
  With ActiveSheet.PivotTables("nEWpIVOTtABLE")
  .PivotFields("Product").Orientation = xlRowField
  .PivotFields("Product").Position = 1
  .PivotFields("Month").Orientation = xlRowField
  .PivotFields("Month").Position = 2
  .AddDataField ActiveSheet.PivotTables( _
  "nEWpIVOTtABLE").PivotFields("Sales"), "Combining Subtotal and Sumif", xlSum
  End With
End Sub

 

[saeed]

شكرا دبراج

However, I'd like to simulate the funtionality of a PV without actually using one. Is there to a way to do that?
It seems to me that a PV is the comfotable front-end to some complicated back-end formulas and operations.
I wish to perform these operations manually without the PV front-end and thus considerably reduce the size of the files. It that a viable thought?

 

[SirJB7]

Hi, saeed!
IMHO it's as viable as reinventing the wheel or rediscovering the fire.
If size matters (file size, I mean ), about files of how many Mb are you talking about?
Regards!