Sum a dynamic sized range

[TheSubject]

Hi all, thanks in advance for any help.
This is really stumping me.

I am making a petty cash sheet for work in Google sheets.
I have columns titled:
A Date,
B Shop,
C Product,
D Quantity,
E Unit,
F Price
G Reciept Total*

Each Receipt is entered more or less manually with some data validation. We are leaving a blank row between each Receipt.

*Receipt Total is what I'm having issues with. I want a formula that I can copy down that basically say:
If there's an entry in column A, then sum that Column G from that row until there is a blank row.

So e.g. First receipt entered in row 2 has three items on it so will take up rows 2, 3 and 4, then row 5 will be blank so the formula would see the entry begins in row 2 and sum G2:G4 ending there as 5 is blank.
Next Receipt might have just 1 item so the formula would sum G6:G6.

Any help would be greatly appreciated!
Thanks,
G

 

[KRice]

Where is the Receipt Total expected to appear? In your example, I'm assuming rows 2, 3, and 4 each have a date appearing in column A? And then there is a blank row 5. You have Quantity, Unit (is this unit price?), Price, and Receipt Total. Is the "Receipt Total" in column G the total cost for only that particular row, or the total of all transactions that are part of this grouping (meaning rows 2, 3, and 4)? Does that grand total appear in G2, G3, G4, G5?

As a side not, generally, I am not in favor of using blank rows like this, as it becomes very easy to corrupt the worksheet if someone adds or removes a blank row. And filtering options are limited because the blank rows limit the extent of the filter. It would be cleaner to avoid the blank rows and add a separate Group column and enter a code that was unique to each set of transactions. For example, rows 2, 3, and 4 would be tagged with group code A, the single row 6 transaction you mention would appear on row 5 now and be tagged with group code B, etc. With this approach, the UNIQUE function could list all unique group codes, and the SUMIF function would automatically filter for only the specified group code of interest and sum all of them.

 

[TheSubject]

Where is the Receipt Total expected to appear? In your example, I'm assuming rows 2, 3, and 4 each have a date appearing in column A? And then there is a blank row 5. You have Quantity, Unit (is this unit price?), Price, and Receipt Total. Is the "Receipt Total" in column G the total cost for only that particular row, or the total of all transactions that are part of this grouping (meaning rows 2, 3, and 4)? Does that grand total appear in G2, G3, G4, G5?

As a side not, generally, I am not in favor of using blank rows like this, as it becomes very easy to corrupt the worksheet if someone adds or removes a blank row. And filtering options are limited because the blank rows limit the extent of the filter. It would be cleaner to avoid the blank rows and add a separate Group column and enter a code that was unique to each set of transactions. For example, rows 2, 3, and 4 would be tagged with group code A, the single row 6 transaction you mention would appear on row 5 now and be tagged with group code B, etc. With this approach, the UNIQUE function could list all unique group codes, and the SUMIF function would automatically filter for only the specified group code of interest and sum all of them.

Hi, thanks for the reply.
To your second point, I half agree. I think you are right in a perfect scenario that is technically more correct etc but I don't think fit for purpose. I'm running a small bar and this is for staff buying a lime or two on their way in, I don't need them inventing unique codes, pens are always short so the code won't be written on the receipts.
If I were to go down that route I would try to make a formula that automatically generated the code, but that formula would still need to distinguish from one purchase to the next, and again I think for the purposes I have the simplest way to do that is leave a 2 row gap, so I'm still back at the same issue, I need a formula to spot 1 row gaps. Can you or anyone advise on this?

The total is for everything that appears on the one receipt, between row gaps. That might be one item, I might have: A: date/today B: local shop C: lime D: 1 E:each F: £0.30 G: £0.30
Or it might be a1 1/3/24 b1 local shop C1-C6: lime, lemon, olives, bread, olive oil, carrier bag, in which case they'll each be priced (so at some later date I can report on e.g. how useless my staff are at taking our own bags bag to the shop so we're not charged, or price check different local stores for limes) but column G will still total the lot in one cell, if it was purchased in one go.

So, any help at all with the formula that I want in column G, that sums up all rows of Column F which appear between two blank rows, please.

 

[KRice]

Well, fit for purpose is certainly important. What version of Excel is being used? You can update your profile with that detail. And where in column G would the total appear?...for example, for the C1-C6 purchase you've described, there are six items belonging to a single total. Where would that total appear?...in G1, G6, etc.?

 

[TheSubject]

Well, fit for purpose is certainly important. What version of Excel is being used? You can update your profile with that detail. And where in column G would the total appear?...for example, for the C1-C6 purchase you've described, there are six items belonging to a single total. Where would that total appear?...in G1, G6, etc.?

It's googlesheets actually- although I do on occasion ask excel questions too - and I don't mind where the sum is, top or bottom makes most sense. In my head easiest place to put it would be where the date is, which would be the first row of any entry but I'm not particularly fussed.

The aim will be to fill the whole column with the formula, probably hidden with "=if(date is here, formula,otherwise " ")" type of arrangement so the formula is already in place, no staff need concern themselves with it, the sum will just appear when they enter a purchase.

 

[KRice]

Here is one idea. Note that I've added a column...seems that if you have a Quantity, a Unit such as each, case, pint, etc., then a total for just that item is needed (that's the new column G), and then the grand total of all items on the receipt appears in column H. There are two formulas here:
The column G formula confirms that something has been entered in the Quantity and Unit Price fields, and if so, those numbers (assuming they are numbers) are multiplied. otherwise a blank (double quotes) is entered.
The column H formula looks up one cell to the left (cell in G column one row above) to confirm that it is either text or blank...this tells us that you want the receipt total to appear on this line. The formula also checks the column G cell immediately to the left to confirm that it is a number (without this extra check, if you pad the bottom of the worksheet with the formula and there are no real entries down there, the formula would otherwise think that it should attempt to compute a total receipt sum and return 0). After these checks, the formula looks down 50 rows in the G column to find the first blank and it creates a dynamic range for values beginning with the value in column G down to the row just above the first blank...and that range is summed.

Book2
	A	B	C	D	E	F	G	H
1	Date	Shops	Product	Quantity	Unit	Unit Price	Total Item Price	Receipt Total
2				2	ea	£ 3.00	£ 6.00	£ 10.00
3				1	ea	£ 4.00	£ 4.00
4
5				3	ea	£ 2.00	£ 6.00	£ 21.00
6				2	ea	£ 6.00	£ 12.00
7				1	ea	£ 3.00	£ 3.00
8
9				3	ea	£ 5.00	£ 15.00	£ 15.00
10
11			lime	2	ea	£ 1.00	£ 2.00	£ 30.80
12			lemon	3	ea	£ 1.00	£ 3.00
13			olives	1	ea	£ 5.00	£ 5.00
14			bread	3	ea	£ 1.50	£ 4.50
15			olive oil	2	ea	£ 8.00	£ 16.00
16			carrier bag	1	ea	£ 0.30	£ 0.30
17
18
19
Sheet1

Cell Formulas
Range	Formula
G2:G19	G2	=IF(AND(D2<>"",F2<>""),D2*F2,"")
H2:H19	H2	=IF(AND(ISNUMBER(G2),N(G1)=0),SUM(G2:INDEX(G2:G51,MATCH("",G2:G51,0)-1)),"")

 

[TheSubject]

Here is one idea. Note that I've added a column...seems that if you have a Quantity, a Unit such as each, case, pint, etc., then a total for just that item is needed (that's the new column G), and then the grand total of all items on the receipt appears in column H. There are two formulas here:
The column G formula confirms that something has been entered in the Quantity and Unit Price fields, and if so, those numbers (assuming they are numbers) are multiplied. otherwise a blank (double quotes) is entered.
The column H formula looks up one cell to the left (cell in G column one row above) to confirm that it is either text or blank...this tells us that you want the receipt total to appear on this line. The formula also checks the column G cell immediately to the left to confirm that it is a number (without this extra check, if you pad the bottom of the worksheet with the formula and there are no real entries down there, the formula would otherwise think that it should attempt to compute a total receipt sum and return 0). After these checks, the formula looks down 50 rows in the G column to find the first blank and it creates a dynamic range for values beginning with the value in column G down to the row just above the first blank...and that range is summed.

Book2
	A	B	C	D	E	F	G	H
1	Date	Shops	Product	Quantity	Unit	Unit Price	Total Item Price	Receipt Total
2				2	ea	£ 3.00	£ 6.00	£ 10.00
3				1	ea	£ 4.00	£ 4.00
4
5				3	ea	£ 2.00	£ 6.00	£ 21.00
6				2	ea	£ 6.00	£ 12.00
7				1	ea	£ 3.00	£ 3.00
8
9				3	ea	£ 5.00	£ 15.00	£ 15.00
10
11			lime	2	ea	£ 1.00	£ 2.00	£ 30.80
12			lemon	3	ea	£ 1.00	£ 3.00
13			olives	1	ea	£ 5.00	£ 5.00
14			bread	3	ea	£ 1.50	£ 4.50
15			olive oil	2	ea	£ 8.00	£ 16.00
16			carrier bag	1	ea	£ 0.30	£ 0.30
17
18
19
Sheet1

Cell Formulas
Range	Formula
G2:G19	G2	=IF(AND(D2<>"",F2<>""),D2*F2,"")
H2:H19	H2	=IF(AND(ISNUMBER(G2),N(G1)=0),SUM(G2:INDEX(G2:G51,MATCH("",G2:G51,0)-1)),"")

Amazing!! That works, or at least close enough that I can play and edit.

I considered your row G solution before but with the variety of options (e.g. one local shop sells ice by the kg, another by the 2kg so if my ice machine breaks as-it did the other day) meanwhile olives are always in a fraction of a kg but my staff are only going to write the number that the receipt says not look at their .634 kg of olives and work out what 1 kg would be...Not to say my way is better still, not certain yet but your formula for row H is what I needed and as I say I can play around from here. Thank you so much!!

 

[KRice]

I think I follow your point...are you saying that the original column F already contains the total price for the item?...that is, the price recorded in F accounts for the unit type, unit price, and quantity? If that is the case, then column G in my last post is not needed, of course...but there is a nuance in the formula that needs some attention. The column G formula was inserting a double quote blank ("") if either of the column D and F cells are blank. If the Total Item Price column G is eliminated, then these "" will not be inserted, and the main Receipt Total function will fail in some cases. To resolve, this idea might work better for you...it uses the ISBLANK function to check for blank cells. But check the results carefully...blanks can be tricky to deal with.

MrExcel_20240301.xlsx
	A	B	C	D	E	F	G
1	Date	Shops	Product	Quantity	Unit	Price	Receipt Total
2				2	ea	£ 6.00	£ 10.00
3				1	ea	£ 4.00
4
5				3	ea	£ 6.00	£ 21.00
6				2	ea	£12.00
7				1	ea	£ 3.00
8
9				3	ea	£15.00	£ 15.00
10
11			lime	2	ea	£ 2.00	£ 30.80
12			lemon	3	ea	£ 3.00
13			olives	1	ea	£ 5.00
14			bread	3	ea	£ 4.50
15			olive oil	2	ea	£16.00
16			carrier bag	1	ea	£ 0.30
17
18
19
20
21
22
23
Sheet2

Cell Formulas
Range	Formula
G2:G23	G2	=IF(AND(ISNUMBER(F2),N(F1)=0),SUM(F2:INDEX(F2:F51,MATCH(TRUE,ISBLANK(F2:F51),0)-1)),"")