CHECK WITHIN FORMULA

[PM1]

In the formula below (which works fine) I want to add a condition.

Col H consists of letters P & C, when it finds a P it sums up the "C"'s until the next instance of P.

Now I want to add into the column 2 more letters K & A, so I want the formula to stop summing when it reaches these characters until the next instance of P.


IF(H2="P",IF(ISNUMBER(MATCH("P",H3:$H$3972,0)),SUM(L2:INDEX(L2:$L$3972,MATCH("P",H3:$H$3972,0))),SUM(L2:$L$3972)),"")/F2

Purch Orders BP²I.xls
	F	G	H	I	J	K	L
2	6	1,266.00	P	2,217		WindowsNTServers	7,596.00
3	6	157.00	C			WindowsNTServers	942.00
4	6	62.00	C			WindowsNTServers	372.00
5	12	174.00	C			WindowsNTServers	2,088.00
6	6	133.00	C			WindowsNTServers	798.00
7	6	169.00	C			WindowsNTServers	1,014.00
8	6	44.00	C			WindowsNTServers	264.00
9	6	35.00	C			WindowsNTServers	210.00
10	1	15.00	C			WindowsNTServers	15.00
11	1	6,365.00	P	13,555		WindowsNTServers	6,365.00
12	2	2,641.00	C			WindowsNTServers	5,282.00
13	2	159.00	C			WindowsNTServers	318.00
14	2	230.00	C			WindowsNTServers	460.00
15	1	44.00	C			WindowsNTServers	44.00
16	3	235.00	C			WindowsNTServers	705.00
17	1	331.00	C			WindowsNTServers	331.00
18	1	35.00	C			WindowsNTServers	35.00
19	1	15.00	C			WindowsNTServers	15.00
20	1	38,960.64	P	38,961		Networks	38,960.64
Assets

 

[Domenic]

Is this what you mean?

=IF(H2="P",IF(ISNUMBER(MATCH(TRUE,INDEX(ISNUMBER(MATCH(H3:$H$3972,{"A","K","P"},0)),0),0)),SUM(L2:INDEX(L2:$L$3972,MATCH(TRUE,INDEX(ISNUMBER(MATCH(H3:$H$3972,{"A","K","P"},0)),0),0)))/F2,SUM(L2:$L$3972)/F2),"")