Finding the previous occurrence of a character in a column

aRush113

New Member
Joined
Nov 10, 2023
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
ABCD
1130P
2255
332025
442045
5525P70
662020
772040
882.542.5
9910P52.5
101045
11
Sheet1
Cell Formulas
RangeFormula
D2:D9D2=IF(C1<>"P",D1+B2,B2)
B10B10=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
 
Upvote 0
Solution
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. 🤣🤣🤣
 
Upvote 0
thanks for the feedback, glad we could halp.

cheers
Rob
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top