Finding the previous occurrence of a character in a column

[aRush113]

In Column "B" i need to sum up column "A" from the row following the "P" in column "C" up to the next occurrence of "P".

A B C
1 € 30,00 P
2 € 5,00
3 € 20,00
4 € 20,00
5 € 25,00 P If C1="" sum(A1:A5) - if C1="P" sum A2:A5
6 € 20,00
7 € 20,00
8 € 2,50
9 € 10,00 P If C5="" sum(A1:A9) - if C5="P" sum A6:A9
10 =SUM(A1:A9)

I need to find the previous "P" row at any time in column "C" and start sum of column "A" from there.
Hope this makes sense.

I've done it before but forgot what i did.
I've been trying for months but my brain is fried and contaminated with all the solutions i came up with.
Is there a KIND soul out there that can help me out?

Thank You

 

[RobP]

Hi,

I'm not entirely clear on your data layout (eg. what goes in what columns), but maybe something like this can help you on your way ?

Book1
	A	B	C	D
1	1	30	P
2	2	5		5
3	3	20		25
4	4	20		45
5	5	25	P	70
6	6	20		20
7	7	20		40
8	8	2.5		42.5
9	9	10	P	52.5
10	10	45
11
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=IF(C1<>"P",D1+B2,B2)
B10	B10	=SUM(A1:A9)

Let me know if that works for you ? (I put the formula in Col D as I couldn't decide on what you were trying to add up..)

So essentially, if your row has a P in it, the value in Col D is your total ?

Cheers
Rob

 

[aRush113]

Yeah, my bad Rob, actually the "ABC" on top and the"123..." on the left are the columns and rows.
But your solution is exactally what i needed. The only problem is that if "C1" does not ="P" it does not work as it should but i resolved that by putting an "IF(C1="",B1,IF(C1<>"P",D1+B2,B2)) only in "D1" and the rest your solution without the "IF" statement.
Thank you and God Bless You, you just cured my insanity.

 

[RobP]

thanks for the feedback, glad we could halp.

cheers
Rob