SUMIF row is equal to test value and row below is '-'

Hi Oscar,

While it is possible to do this it will be a little awkward, involving extra columns. It would be much simpler to stop using the "-" and use the actual names instead. Checkout the popup format to create a list taht makes it easy to list the names.

quinn

Hi Oscar,

Here is a quick and dirty way to fix your list.

You need an extra column for each - you want to eliminate. So to deal with billy's forth - I would need to add another column. If it was me, I would copy the final column and "paste formula results" into the original column and use the popup form then on.

quinn

Hi Oscar,

You might find it helpful to develop another way to distinguish grouped transactions. With your current approach if you want to isolate Jess' profit from an individual transaction you have another awkward formula to create.

quinn

Ozzmystro wrote:

the "-" indicates that the transaction was part of the same group, rather than being a completely separate event, which helps make it clearer when you are looking over the spreadsheet.

Instead of a - have you considered using Conditional Highlighting?

For example it is easy to achieve an effect like this:

Select the cells in the column, click Conditional Highlighting in the pane at the right and Add a Rule:

Click the "box with the arrow":

Then click cell A1 (the cell immediately above the first of the selected cells).

If you don't 'Preserve row' this highlighting rule will have each cell compare itself to the one immediately above it.

Then change the Bold to Custom Style (you have to scroll down in the list to see that):

I chose a "grayer" text color when I defined the Custom Style. You could of course do something else to suit your taste, even make the contents "disappear."

By using Conditional Highlighting in this way you can avoid entering the - that complicate your formulas and table setup and simply use the SUMIF on the original column as you normally would.

SG

What immediately comes to mind is that groups seem to be important to your analysis. If that is the case, then you may find it helpful to add a Group column and assign each transaction to a group. Then in pulling summary statistics you can use the Group column with SUMIF. Or use SUMIFS referencing both the Name and Group columns. As you probably know, SUMIF works with one column-condition pair. SUMIFS can have multiple column-condition pairs.

SG

Hi Oscar,

I think the additional column for your groups is a really good one. I don't know if it is the case, but if both Jess and Richard have profit from the same group you would then be able to acress that data in that context.

quinn

Hi Oxxmystro,

While you can't put an IF into a conditional highlighting rule, you can do what you want- with and extra column that has an IF.

This column can be hidden.

Then we compare your target cell to this with a conditional highlighting rule.

quinn

Hi Oxxmystro,

Glad to help.

quinn