Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How do I combine data in a chart?

I'd like to make a bar chart using data from a table similar to this...


User uploaded file


The chart should combine the names that are the same, and add up the total amount that they have paid. So it would look a little like this...


User uploaded file


But all my attempts so far has treated that list of people as separate people.


I'm using Numbers 3.5.3.


Thank you!

MacBook Pro (Retina, Mid 2012), OS X Yosemite (10.10.3)

Posted on May 3, 2015 9:45 AM

Reply
Question marked as Best reply

Posted on May 4, 2015 5:44 AM

Hi Shadowplay,


I would suggest a new table with your clients listed in the left column and the formula =SUMIF(OriginalTable::A,A2,OriginalTable::B) filled down in column B.

This will give you your clients with their total Paid. Should be easy to get your chart from there.


quinn

3 replies

May 4, 2015 5:45 AM in response to Shadowplay12

Hello


You'd need to prepare a summary table holding total per name to create such chart. Something like this.


User uploaded file



Data A1 Client A2 John A3 Rebecca A4 Harry A5 John A6 John A7 Rebecca A8 John A9 B1 Paid B2 100 B3 50 B4 25 B5 50 B6 100 B7 50 B8 200 B9 C1 index C2 =IF(AND(LEN(A2)>0,COUNTIF(A$2:A2,A2)=1),MAX(C$1:C1)+1,"") C3 =IF(AND(LEN(A3)>0,COUNTIF(A$2:A3,A3)=1),MAX(C$1:C2)+1,"") C4 =IF(AND(LEN(A4)>0,COUNTIF(A$2:A4,A4)=1),MAX(C$1:C3)+1,"") C5 =IF(AND(LEN(A5)>0,COUNTIF(A$2:A5,A5)=1),MAX(C$1:C4)+1,"") C6 =IF(AND(LEN(A6)>0,COUNTIF(A$2:A6,A6)=1),MAX(C$1:C5)+1,"") C7 =IF(AND(LEN(A7)>0,COUNTIF(A$2:A7,A7)=1),MAX(C$1:C6)+1,"") C8 =IF(AND(LEN(A8)>0,COUNTIF(A$2:A8,A8)=1),MAX(C$1:C7)+1,"") C9 =IF(AND(LEN(A9)>0,COUNTIF(A$2:A9,A9)=1),MAX(C$1:C8)+1,"")




Summary A1 Client A2 =IFERROR(INDEX(Data::A,MATCH(ROW()-1,Data::C,0),1),"") A3 =IFERROR(INDEX(Data::A,MATCH(ROW()-1,Data::C,0),1),"") A4 =IFERROR(INDEX(Data::A,MATCH(ROW()-1,Data::C,0),1),"") A5 =IFERROR(INDEX(Data::A,MATCH(ROW()-1,Data::C,0),1),"") A6 =IFERROR(INDEX(Data::A,MATCH(ROW()-1,Data::C,0),1),"") A7 =IFERROR(INDEX(Data::A,MATCH(ROW()-1,Data::C,0),1),"") A8 =IFERROR(INDEX(Data::A,MATCH(ROW()-1,Data::C,0),1),"") A9 =IFERROR(INDEX(Data::A,MATCH(ROW()-1,Data::C,0),1),"") B1 Paid B2 =IF(LEN(A2)>0,SUMIF(Data::A,A2,Data::B),"") B3 =IF(LEN(A3)>0,SUMIF(Data::A,A3,Data::B),"") B4 =IF(LEN(A4)>0,SUMIF(Data::A,A4,Data::B),"") B5 =IF(LEN(A5)>0,SUMIF(Data::A,A5,Data::B),"") B6 =IF(LEN(A6)>0,SUMIF(Data::A,A6,Data::B),"") B7 =IF(LEN(A7)>0,SUMIF(Data::A,A7,Data::B),"") B8 =IF(LEN(A8)>0,SUMIF(Data::A,A8,Data::B),"") B9 =IF(LEN(A9)>0,SUMIF(Data::A,A9,Data::B),"")




Notes.


Formulae in Data::C2, Summary::A2, Summary::B2 can be filled down.


Data::C records the indices of distinct values in Data::A.


Summary::A retrieves distinct names from Data::A based upon the indices in Data::C.


Summary::B calculates the sum per name.


Tables and Chart are created with Numbers v2.



Hope this may help,

H

How do I combine data in a chart?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.