Sum Corresponding Values Till Blank

[JMata806]

Greetings I am attempting to figure a way to make my invoicing a bit easier.
Looking at the invoice I see several "Credits" which is great but we want to make sure it is for the proper amount.
The issue is the unique items show up in multiple lines prior to the credit so there is no direct sum I can do normally.

I tried to mimic a similar idea (How to sum till blank cell in dynamic way?)

I've tried breaking into basic IF's or SUMIF's but even with an AGGREVETATE, it's being a bit of a pain.
Perhaps I am overthinking or just boggled but any help is appreciated.

PRODUCT​	PRICE​
APPLE - GALA	1.00
APPLE - HONEYDEW	1.00
CREDIT	-0.50
ORANGE - EASY PEEL	1.50
CREDIT	-0.50
BANANA - MONTE FRESC	1.25
BANANA - MONTE FRESC	1.25
BANANA - ORGANIC ALB	1.25
CREDIT	-1.50

So in this scenario, I have several items above and for each item, I am given a credit of $0.25 (this credit amount could change depending on the item).


Now manually I'd have to:

1. Apples (Add the 2 at $1.00 apiece for $2.00), (Credit for 2 Apples is 0.25 a piece so it's 0.50)
2. Orange (Add the 1 at $1.50 apiece for $1.50), (Credit for 1 Orange is 0.50 a piece so it's 0.50)
3. Banana (Add the 3 at $1.25 apiece for $3.75), (Credit for 3 Bananas is 0.50 a piece so it's 1.50)

But I know there is a way to break down the sum of these using when the credit line appears. IE SUM APPLES, SUM ORANGE, SUM BANANA till the next credit line.
The credit line does not change and is the same ID # every time so it's a key component that I can use as a factor.

Any help is greatly appreciated!

 

[jtakw]

Hi,

Would something like this help:

Book3.xlsx
	A	B	C	D	E	F
1	Product	Price
2	APPLE - GALA	1			Price	Credit
3	APPLE - HONEYDEW	1		Orange	1.5	0.5
4	CREDIT	-0.5		Banana	3.75	1.5
5	ORANGE - EASY PEEL	1.5		Apple	2	0.5
6	CREDIT	-0.5
7	BANANA - MONTE FRESC	1.25
8	BANANA - MONTE FRESC	1.25
9	BANANA - ORGANIC ALB	1.25
10	CREDIT	-1.5
Sheet1021

Cell Formulas
Range	Formula
E3:E5	E3	=SUMPRODUCT((LEFT(A$2:A$10,LEN(D3))=D3)*(B$2:B$10))
F3:F5	F3	=-SUMPRODUCT((A$2:A$10="Credit")*(LEFT(OFFSET(A$2:A$10,-1,0),LEN(D3))=D3)*B$2:B$10)

 

[JMata806]

Close I tried this before but since the breakdown differs it gives me Value errors.

Maybe seeing the actual data could help I've censored some private info for customer sake.

So as you can see I have several items with Product Codes, but the credit is always going to be PD99999998.
For the most part, the credit always follows the product.

But as you can see in rows 3 & 4 I have two products (0012 & 0012Q) before I get the credit report.

My end goal is to Add these rows regardless of its 1 row or 1000 rows every time it is between PD99999998.