Ozzmystro
Level 1 (5 points)
Photos for Mac

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

Hello,

 

I have a numbers spreadsheet where one column has a list of names that are mixed up with repeats, and another column that has a list of profit values relating to that name.

 

Now, I want to determine the total profit made from each of the names so I can see the total profit per name.

 

That part is easy, I just have a SUMIF function that checks if the name matches a specific name and then adds that profit to the total.

 

The problem I am having is that in the names column, sometimes I will have a name and then the next few rows are just '-' indicating that they are the same name. The SUMIF function I use does not take into account these values because they obviously don't match the name of interest.

 

So my question is: Is it possible to create a function that will check for a matching name, and then if the next row is '-', then add that value to the total as well. This has to work with multiple rows of '-' following a name.

 

The following screenshot is an example of what I mean since I realise that this may not make much sense.

 

Spreadsheet Example.PNG

So in this case, Jess's total profit would be = 5 + 35 + 15 + 5 + 15 = 75

and Gill's profit = 30 + 30 + 20 + 40 = 120

 

I hope I've made this clear enough. Thank you in advance!

 

Oscar

Posted on Sep 2, 2016 5:54 AM

by t quinn,Solvedanswer
t quinn t quinn
Level 5 (4,930 points)
Mac OS X
A:

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.

Screen Shot 2016-09-05 at 7.11.17 AM.png

This column can be hidden.

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

Screen Shot 2016-09-05 at 7.12.12 AM.png

 

quinn

Posted on Sep 5, 2016 6:16 AM

Close
Ozzmystro

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

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Sep 2, 2016 6:27 AM in response to Ozzmystro
    Level 5 (4,930 points)
    Mac OS X
    Sep 2, 2016 6:27 AM in response to Ozzmystro

    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

  • by t quinn,Helpful

    t quinn t quinn Sep 2, 2016 7:00 AM in response to t quinn
    Level 5 (4,930 points)
    Mac OS X
    Sep 2, 2016 7:00 AM in response to t quinn

    Hi Oscar,

     

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

    Screen Shot 2016-09-02 at 7.36.05 AM.png

    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

  • by Ozzmystro,

    Ozzmystro Ozzmystro Sep 2, 2016 6:59 AM in response to t quinn
    Level 1 (5 points)
    Photos for Mac
    Sep 2, 2016 6:59 AM in response to t quinn

    Hi Quinn, thank you for your fast response.

     

    I realise that it would be easier if all the "-" were replaced with the names, but the reason I have them like this is because 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.

     

    As for your "quick and dirty" method, I shall create a separate sheet that I can have all those extra columns in, and then it won't mess up the original.

     

    If you (or anyone else) can think of a more convenient method for achieving the same result, then I'd be grateful to hear it.

     

    Thanks for a quick solution though!

  • by t quinn,

    t quinn t quinn Sep 2, 2016 7:11 AM in response to Ozzmystro
    Level 5 (4,930 points)
    Mac OS X
    Sep 2, 2016 7:11 AM in response to Ozzmystro

    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

  • by SGIII,Helpful

    SGIII SGIII Sep 2, 2016 9:02 AM in response to Ozzmystro
    Level 6 (10,622 points)
    Mac OS X
    Sep 2, 2016 9:02 AM in response to Ozzmystro

    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:

     

    Screen Shot 2016-09-02 at 11.33.38 AM.png

     

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

     

    Screen Shot 2016-09-02 at 11.35.48 AM.png

     

     

    Click the "box with the arrow":

     

    Screen Shot 2016-09-02 at 11.36.45 AM.png

     

     

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

     

    Screen Shot 2016-09-02 at 11.38.37 AM.png

     

    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):

     

    Screen Shot 2016-09-02 at 11.40.57 AM.png

     

    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

  • by Ozzmystro,

    Ozzmystro Ozzmystro Sep 2, 2016 9:02 AM in response to SGIII
    Level 1 (5 points)
    Photos for Mac
    Sep 2, 2016 9:02 AM in response to SGIII

    Hi SG, thanks for your reply.

     

    I do actually use conditional highlighting throughout my spreadsheet which is extremely useful.

     

    However, the technique you suggest wouldn't work in all cases. This is because if I had, say, 4 transactions from "jess" that were all part of the same group, and then following that I had another transaction from "Jess" that wasn't part of that group, then your technique would grey out that transaction even though it's not part of the group.

     

    You are right though, something along those lines would be the best way of achieving the desired outcome and allow me to easily use the SUMIF function.

     

    Can you think of a way that would allow me to achieve this?

     

    Thanks everyone for your input so far!

  • by SGIII,

    SGIII SGIII Sep 2, 2016 9:08 AM in response to Ozzmystro
    Level 6 (10,622 points)
    Mac OS X
    Sep 2, 2016 9:08 AM in response to Ozzmystro

    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

  • by t quinn,

    t quinn t quinn Sep 2, 2016 9:15 AM in response to Ozzmystro
    Level 5 (4,930 points)
    Mac OS X
    Sep 2, 2016 9:15 AM in response to Ozzmystro

    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

  • by Ozzmystro,

    Ozzmystro Ozzmystro Sep 5, 2016 5:56 AM in response to Ozzmystro
    Level 1 (5 points)
    Photos for Mac
    Sep 5, 2016 5:56 AM in response to Ozzmystro

    Hi,

     

    In the end, I used Quinn's method to replace the original column with the names, and then instead of creating a group column (which is a good idea but doesn't necessarily apply to my spreadsheet), I have coloured the names so that they blend in with the background colour, ie. they aren't visible.

     

    I couldn't work out a way to use conditional formatting to colour the names so I've resorted to doing this manually.

     

    Is there a way of using conditional formatting that references the column next to it? ie. using an IF statement? It doesn't look like this is possible. The reason I ask is because the second column either has a number or "-" and I want the colour of the name to be grey if the column next to it is "-".

     

    Not sure if we're going round in circles here. Thanks for all your help anyway guys!

  • by t quinn,Solvedanswer

    t quinn t quinn Sep 5, 2016 6:16 AM in response to Ozzmystro
    Level 5 (4,930 points)
    Mac OS X
    Sep 5, 2016 6:16 AM in response to Ozzmystro

    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.

    Screen Shot 2016-09-05 at 7.11.17 AM.png

    This column can be hidden.

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

    Screen Shot 2016-09-05 at 7.12.12 AM.png

     

    quinn

  • by Ozzmystro,

    Ozzmystro Ozzmystro Sep 6, 2016 5:13 AM in response to t quinn
    Level 1 (5 points)
    Photos for Mac
    Sep 6, 2016 5:13 AM in response to t quinn

    Perfect! I have now implemented your idea and it works as suggested.

     

    Thanks for all your help!

  • by t quinn,

    t quinn t quinn Sep 6, 2016 5:52 AM in response to Ozzmystro
    Level 5 (4,930 points)
    Mac OS X
    Sep 6, 2016 5:52 AM in response to Ozzmystro

    Hi Oxxmystro,

     

    Glad to help.

     

    quinn