SUBTOTAL (average) on criteria

[colaps]

Hello,

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

Zone	StoreID <tbody> </tbody>	Sortiments <tbody> </tbody>	SoldQty(Kg) <tbody> </tbody>
EU	SHOP1 <tbody> </tbody>	APPLE	200
EU	SHOP1 <tbody> </tbody>	ORANGE	140
EU	SHOP1 <tbody> </tbody>	ONION <tbody> </tbody>	21
EU	SHOP1 <tbody> </tbody>	POTATO <tbody> </tbody>	47
EU	SHOP1 <tbody> </tbody>	GRAPES <tbody> </tbody>
EU	SHOP1 <tbody> </tbody>	LEMON <tbody> </tbody>	589
EU	SHOP1	EGG <tbody> </tbody>
EU	SHOP3	APPLE	34
EU	SHOP3	ORANGE
EU	SHOP3	ONION <tbody> </tbody>	450
EU	SHOP3	POTATO
EU	SHOP3	GRAPES	243
EU	SHOP3	LEMON	n/a
EU	SHOP3	EGG
US	SHOP2	APPLE	230
US	SHOP2	ORANGE	144
US	SHOP2	ONION <tbody> </tbody>	200
US	SHOP2	POTATO <tbody> </tbody>	85
US	SHOP2	GRAPES <tbody> </tbody>
US	SHOP2	LEMON <tbody> </tbody>	254
US	SHOP2	EGG <tbody> </tbody>	n/a

<tbody>
</tbody>

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:

Zone	Sortiment	Average
EU	APPLE
	ORANGE
	ONION
	POTATO
	GRAPES
	LEMON
	EGG <tbody> </tbody>
US	APPLE
	ORANGE
	ONION
	POTATO
	GRAPES
	LEMON
	EGG

<tbody>
</tbody>

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.