Column A: Name of buyer
Column B: Product
Column C= Price of product.

Example:
John Apples 3
Jane Pears 4
John Grapes 5

Now I want to include a TOTAL, which sums the amount they have spent on all products

Example:
John Apples 3
Jane Pears 4
John Grapes 5
John TOTAL 8 (3+5)

But I want to continue adding rows, so it may look like this later:

John Apples 3
Jane Pears 4
John Grapes 5
John TOTAL 15 (3+5+7)
John Carrots 7

My problem is, how to set up the SUM formula. Obviously you cannot sum a list of values that includes the sum itself, so it must add up the range, excluding itself.

Best practices would put the TOTAL at the beginning of the range or at the end of the range, but then i wouldn't need to post this

Thanks!

Gabethebabe,

I think that sumif is likely to be the answer.

I just ask chatgpt for all my MS Excel help these days

Tried that

The formula won't work if you include the field that also contains the sum in the summing range

I need some way to exclude the sum cell or grab the sum of the range above and the range below the sum cell.

Hmmm, now that I think of it, dividing into two ranges might be much easier than excluding a cell from a range

Maybe instead of inserting a row for each total, you make the running total in a separate column. Or you can use the sun if formula. Use sumif to get the total including the total line, minus a sumifs formula that is only looking for lines that say John international first column and total in the second column, so that you are subtracting out all of the totals.

Or just use one sumif formula with 2 conditions. The first condition is that that the first column is equal to John and the second column is not equal to total.

But it would be helpful to know what exactly you are trying to accomplish with this spreadsheet. I wonder if there isn't an easier/cleaner way to do what you are trying to do.

At the end I managed it, using sumif and splitting the one range with exception row into a range above and a range below the row. That was not hard, at the end.