menick
Level 1 (8 points)
iWork

Q: Sum total income for each unique client and chart it!?

Greetings from sunlit Athens!  I have been scouring for the past 5 hours for an elegant solution to the following:

 

I would like to sum the total amount of income for each unique client and chart it to visualise which customers bring in the most cash.

 

For example from this to this:

ClientsInvoiced €
A100
B200
C250
A130
A120
B100

 

Clients

€ Total
A350
B300
C250

 

How can this be implemented in Numbers? As elegantly as possible?

MacBook Pro (Retina, 15-inch, Mid 2015), OS X El Capitan (10.11.6)

Posted on Sep 3, 2016 4:38 PM

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

Hi menick,

Here is an approach that uses an index column in your invoiced table. This can be hidden.

Screen Shot 2016-09-04 at 7.23.18 AM.png

C2= IF(COUNTIF(A$2:A2,A2)=1,C1+1,C1)

Filled down.

You can use INDEX/MATCH to bring your unique clients over to a second table.

Screen Shot 2016-09-04 at 7.26.26 AM.png

A2= INDEX(Table 1::A,MATCH(ROW(cell)−1,Table 1::C,-1))

Filled down.

You can hide the error cells (included for when your client list expands) with a filter or by wrapping the formula with IFERROR() once you get it working.

 

quinn

Posted on Sep 6, 2016 7:17 AM

Close
menick

Q: Sum total income for each unique client and chart it!?

  • All replies
  • Helpful answers

  • by t quinn,Helpful

    t quinn t quinn Sep 3, 2016 10:46 PM in response to menick
    Level 5 (4,930 points)
    Mac OS X
    Sep 3, 2016 10:46 PM in response to menick

    Hi menick,

     

    SUMIF() will do the job.

    Screen Shot 2016-09-03 at 6.27.52 PM.png

    the formula in Table 2::B2=

    SUMIF(Table 1::A,A2,Table 1::B)

    It is filled down.

     

    quinn

  • by SGIII,

    SGIII SGIII Sep 3, 2016 6:09 PM in response to menick
    Level 6 (10,622 points)
    Mac OS X
    Sep 3, 2016 6:09 PM in response to menick

    menick wrote:

     

     

    and chart it to visualise which customers bring in the most cash.

     

     

     

    To see what charts might work best I recommend having a look at the 'Charting Basics' template at File > New in your menu.  Generally, the approach is to select the cells in the table to be charted, in your case the second table, and then choose a chart type, either after clicking 'Chart' in the toolbar or choosing from the menu Insert > Chart and choose the type.

     

    SG

  • by menick,

    menick menick Sep 3, 2016 10:46 PM in response to t quinn
    Level 1 (8 points)
    iWork
    Sep 3, 2016 10:46 PM in response to t quinn

    It certainly will do the job, but only after what I'm looking for. I would like the list of clients to be dynamically created  and simplified to one copy per client, before the sumif takes place.  If there was any magical way to do this without a second table even better!!...

  • by t quinn,

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

    Hi menick,

     

    I don't understand what you are imagining when you say "do this without a second table". Where would your generated list of unique clients appear? If you want, you can use SUMIF() in your first table.

     

    quinn

  • by t quinn,Solvedanswer

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

    Hi menick,

    Here is an approach that uses an index column in your invoiced table. This can be hidden.

    Screen Shot 2016-09-04 at 7.23.18 AM.png

    C2= IF(COUNTIF(A$2:A2,A2)=1,C1+1,C1)

    Filled down.

    You can use INDEX/MATCH to bring your unique clients over to a second table.

    Screen Shot 2016-09-04 at 7.26.26 AM.png

    A2= INDEX(Table 1::A,MATCH(ROW(cell)−1,Table 1::C,-1))

    Filled down.

    You can hide the error cells (included for when your client list expands) with a filter or by wrapping the formula with IFERROR() once you get it working.

     

    quinn

  • by menick,

    menick menick Sep 6, 2016 7:17 AM in response to t quinn
    Level 1 (8 points)
    iWork
    Sep 6, 2016 7:17 AM in response to t quinn

    Great, I can get the list populated so far. What about the formula inside the invoice column? How do I sum for each client?

  • by menick,

    menick menick Sep 6, 2016 7:19 AM in response to t quinn
    Level 1 (8 points)
    iWork
    Sep 6, 2016 7:19 AM in response to t quinn

    Also the formula shouldn't be separated by commas, but rather semi-colons. Otherwise there is an error in parsing

  • by t quinn,

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

    Hi menick,

     

    Is this what you are looking for?

    Screen Shot 2016-09-06 at 8.21.07 AM.png

    In my region Numbers uses commas but it is an easy transition to make. I still like to create formulas in Numbers by pointing and clicking. It avoids many syntax errors that way.

     

    quinn

  • by menick,

    menick menick Sep 6, 2016 7:27 AM in response to t quinn
    Level 1 (8 points)
    iWork
    Sep 6, 2016 7:27 AM in response to t quinn

    Yes, I figured it out from the first reply as I was writing! Unfortunatelly I can't set two replies as helpful, which it is!!... Thank you all for your time!

  • by t quinn,

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

    Hi menick,

     

    Glad everything is working for you. Is your question solved?

    edit: I guess it is! thanks for the green check!

     

    quinn

  • by t quinn,

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

    Hi menick,

     

    Check back when you start exploring the charting options. There are some people here with some real expertise.

     

    quinn