SUBTOTAL (average) on criteria

[colaps]

Hello,

I have an issue and i need your assistance
Situation is like this:


[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Zone[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]StoreID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR="class: grid"]
[TD="width: 161"]Sortiments[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR="class: grid"]
[TD="width: 81"]SoldQty(Kg)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]SHOP1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]APPLE[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]SHOP1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ORANGE[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]SHOP1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR="class: grid"]
[TD="width: 161"]ONION[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]SHOP1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR="class: grid"]
[TD="width: 161"]POTATO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]47
[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]SHOP1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR="class: grid"]
[TD="width: 161"]GRAPES[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]SHOP1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR="class: grid"]
[TD="width: 161"]LEMON[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]589
[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]SHOP1[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR="class: grid"]
[TD="width: 161"]EGG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]SHOP3[/TD]
[TD]APPLE[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]SHOP3[/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]SHOP3[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR="class: grid"]
[TD="width: 161"]ONION[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]SHOP3[/TD]
[TD]POTATO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]SHOP3[/TD]
[TD]GRAPES[/TD]
[TD]243[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]SHOP3[/TD]
[TD]LEMON[/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]SHOP3[/TD]
[TD]EGG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]SHOP2[/TD]
[TD]APPLE[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]SHOP2[/TD]
[TD]ORANGE[/TD]
[TD]144[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]SHOP2[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR]
[TD="width: 161"]ONION[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]SHOP2[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR]
[TD="width: 161"]POTATO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]SHOP2[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR]
[TD="width: 161"]GRAPES[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]SHOP2[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR]
[TD="width: 161"]LEMON[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]254[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]SHOP2[/TD]
[TD][TABLE="width: 161"]
<tbody>[TR]
[TD="width: 161"]EGG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]n/a[/TD]
[/TR]
</tbody>[/TABLE]



i need to have the average on the values from column " SoldQty(Kg) " in the table below.
Filter criteria is on Zone ( EU and US , and on sortiment type )

The result should look like:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Zone[/TD]
[TD]Sortiment[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]EU[/TD]
[TD]APPLE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ONION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]POTATO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GRAPES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]LEMON[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 161"]
<tbody>[TR="class: grid"]
[TD="width: 161"]EGG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]APPLE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ORANGE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ONION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]POTATO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GRAPES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]LEMON[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]EGG[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


average value for each of the sortiment ( APPLE, ONION , etc ) but for the corresponding Zone.


My idea was to use SUBTOTAL(101,range) to get the AVERAGE values even if the values are blank or text, and at the same time i need to make sure that i can have the criteria for the zone , i`m stuck at this now.

Any help is welcomed.

Thank you.

 

[DanteAmor]

Hi Colaps, You can create a Pivot Table as shown:

 

[MPaul100]

If you get rid of the merged cells in results table, then: =IFERROR(AVERAGEIFS(D:D,A:A,LOOKUP(2,1/($L$3:L3<>""),$L$3:L3),C:C,M3),"")

 

[colaps]

@DanteAmor

Hello,

Yes that will work but doesn`t fit to my actual data layout. Thanks anyways.

 

[colaps]

@MPaul100

This is exactly what i was looking for. Big Thanks !

Regards.

 

[DanteAmor]

@DanteAmor

Hello,

Yes that will work but doesn`t fit to my actual data layout. Thanks anyways.

Hi, ok and good luck, thanks for the feedback.