Calculate average for first value(s) after a pre-defined text marker

[smide]

Hello.




In column B (B2:B600) I have a product list (about 25 different products) and in column E their prices.
In column D I have a list of markers for each product. Markers are S - sold, W - waiting and A - away.


In column G (from cell G2 and downwards) I have list off ALL potential products.


I need a formula to find average of first price occurrence for each product IF the marker for previous occurrence for that product was A (away) and to put those results/averages in Column H.


example.


(for simplicity only for product 'apple')

	A	B	C	D	E	F	G	H
1
2		apple		S	12		apple	12.5
3		sugar			6		sugar	....
4		apple		A	14		orange	....
5		orange			28		....
6		sugar			5
7		apple		W	9
8		sugar			32
9		orange			11
10		apple		A	8
11		apple		S	16
12		sugar			20
13		orange			3
14		sugar			19
15		apple		W	9
16		sugar			5
17		....			....

<tbody>
</tbody>

​

cell H2 is average value for apple (first price after marker A for previous apple product): =average (E7,E11) = average(9,16) = 12.5

 

[Tetra201]

Here is a monstrous array formula (to be entered using Ctrl+Shift+Enter, not just Enter) that returns the desired result for the posted dataset.
Caveat: "apple A" must not be the last entry for "apple".
Hope somebody can offer a shorter and more elegant solution.

=SUM(IF(INDEX($D$2:$D$600,N(IF(,,SMALL(IF($B$2:$B$600=G2,ROW($B$2:$B$600)-ROW($B$2)+1),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$600,G2)))))))="A",INDEX(INDEX($E$2:$E$600,N(IF(,,SMALL(IF($B$2:$B$600=G2,ROW($B$2:$B$600)-ROW($B$2)+1),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$600,G2))))))),N(IF(,,ROW(INDIRECT("1:"&COUNTIF($B$2:$B$600,G2)))+1)))))/COUNTIFS($B$2:$B$600,G2,$D$2:$D$600,"A")

 

[Peter_SSs]

If I am understanding correctly, the cells to be averaged in your example would be E7 and E11.
Would it be possible for you to have data as shown below (only change I have made is D2 from "S" to "A")?
If that is possible, should cell E14 also be included in the average, even though its own marker is "A"?

Book1
	B	C	D	E
2	apple		A	12
3	sugar			6
4	apple		A	14
5	orange			28
6	sugar			5
7	apple		W	9
8	sugar			32
9	orange			11
10	apple		A	8
11	apple		S	16
12	sugar			20
13	orange			3
14	sugar			19
15	apple		W	9
16	sugar			5
Sheet1