Sum Corresponding Values Till Blank

JMata806

New Member
Joined
May 26, 2021
Messages
12
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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 - GALA1.00
APPLE - HONEYDEW1.00
CREDIT-0.50
ORANGE - EASY PEEL1.50
CREDIT-0.50
BANANA - MONTE FRESC1.25
BANANA - MONTE FRESC1.25
BANANA - ORGANIC ALB1.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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi,

Would something like this help:

Book3.xlsx
ABCDEF
1ProductPrice
2APPLE - GALA1PriceCredit
3APPLE - HONEYDEW1Orange1.50.5
4CREDIT-0.5Banana3.751.5
5ORANGE - EASY PEEL1.5Apple20.5
6CREDIT-0.5
7BANANA - MONTE FRESC1.25
8BANANA - MONTE FRESC1.25
9BANANA - ORGANIC ALB1.25
10CREDIT-1.5
Sheet1021
Cell Formulas
RangeFormula
E3:E5E3=SUMPRODUCT((LEFT(A$2:A$10,LEN(D3))=D3)*(B$2:B$10))
F3:F5F3=-SUMPRODUCT((A$2:A$10="Credit")*(LEFT(OFFSET(A$2:A$10,-1,0),LEN(D3))=D3)*B$2:B$10)
 
Upvote 0
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.
1645821394177.png


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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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