Excel for summarizing/arranging data

N

naar

New Member
Joined
Jan 28, 2013
Messages
11
hi, i have been an assignment by my teacher to arrange data in excel. the data is about some type of customer database which contains ID of Customer, Outgoing calls, Incoming calls and sms, each customer ID Making several calls

the data should be arranged in such format
Customer ID Incoming Calls Outgoing Calls SMS Total
32323 2 4 2 8

Please let me what formula should i apply so that Excel automatically calculates, incoming/outgoing/sms per customer id from the large database...

Any help would be appreciated

Regards

A. Kumar
 
Sort by date Sort by votes
Al Chara said:
You could use something simple like SUMIF or use a PivotTable to summarize the table.
Click to expand...
Thanks dear for the quick answer
actually what i want is not the sum but the frequency of incoming/outgoing calls per customer id... something just like COUNTIF but the problem starts that the type of call (incoming/outgoing) is mentioned in a separate column... I will try pivot table, i will be much thankful if you may provide some basic detail for pivot table...
Thanks once again
 
Upvote 0
naar said:
hi, i have been an assignment by my teacher to arrange data in excel. the data is about some type of customer database which contains ID of Customer, Outgoing calls, Incoming calls and sms, each customer ID Making several calls

the data should be arranged in such format
Customer ID Incoming Calls Outgoing Calls SMS Total
32323 2 4 2 8

Please let me what formula should i apply so that Excel automatically calculates, incoming/outgoing/sms per customer id from the large database...

Any help would be appreciated

Regards

A. Kumar
Click to expand...

May be you want this one... Just type the ID in A2


Sheet3

*ABCDE
*****
*****

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:102px;"><col style="width:102px;"><col style="width:102px;"><col style="width:102px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Customer ID[/TD]
[TD="align: center"]Incoming Calls[/TD]
[TD="align: center"]Out Going Calls[/TD]
[TD="align: center"]SMS[/TD]
[TD="align: center"]Total[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]110[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Customer ID[/TD]
[TD="align: center"]Incoming Calls[/TD]
[TD="align: center"]Out Going Calls[/TD]
[TD="align: center"]SMS[/TD]
[TD="align: center"]Total[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]36[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]1212[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]45[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]55[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]1255[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]1212[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]47[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]19[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]1212[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]58[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B2=SUMIF(A6:A12,A2,B6:B12)
C2=SUMIF(A6:A12,A2,C6:C12)
D2=SUMIF(A6:A12,A2,D6:D12)
E2=SUM(B2:D2)
E6=SUM(B6:D6)
E7=SUM(B7:D7)
E8=SUM(B8:D8)
E9=SUM(B9:D9)
E10=SUM(B10:D10)
E11=SUM(B11:D11)
E12=SUM(B12:D12)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:
Upvote 0
naar said:
Thanks dear for the quick answer
actually what i want is not the sum but the frequency of incoming/outgoing calls per customer id... something just like COUNTIF but the problem starts that the type of call (incoming/outgoing) is mentioned in a separate column... I will try pivot table, i will be much thankful if you may provide some basic detail for pivot table...
Thanks once again
Click to expand...

Please provide a larger sample of how your data is organized (no sensitive information).
 
Upvote 0
proficient said:
May be you want this one... Just type the ID in A2


Sheet3

*ABCDE
*****
*****

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Customer ID[/TD]
[TD="align: center"]Incoming Calls[/TD]
[TD="align: center"]Out Going Calls[/TD]
[TD="align: center"]SMS[/TD]
[TD="align: center"]Total[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]110[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Customer ID[/TD]
[TD="align: center"]Incoming Calls[/TD]
[TD="align: center"]Out Going Calls[/TD]
[TD="align: center"]SMS[/TD]
[TD="align: center"]Total[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]36[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]1212[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]45[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]55[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]1255[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]1212[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]47[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]1111[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]19[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]1212[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]58[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B2=SUMIF(A6:A12,A2,B6:B12)
C2=SUMIF(A6:A12,A2,C6:C12)
D2=SUMIF(A6:A12,A2,D6:D12)
E2=SUM(B2:D2)
E6=SUM(B6:D6)
E7=SUM(B7:D7)
E8=SUM(B8:D8)
E9=SUM(B9:D9)
E10=SUM(B10:D10)
E11=SUM(B11:D11)
E12=SUM(B12:D12)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...



Thanks for the help, this is the result what i want but there is a problem
the data is not in the format as you have supposed
just suppose the data in the left that is 10,000 plus records with more than 700 hundred ids repeating again and again sometimes with 'outgoing' as call type sometimes as 'incoming' and sometimes just 'sms' without anything specified in call type. I have filtered out the unique values, using custom filter, filtered incoming and outgoing, sms list and then used countif function
[TABLE="width: 603"]
<tbody>[TR]
[/TR]
[TR]
[TD="class: xl2510812, width: 84"]Customer ID[/TD]
[TD="class: xl2510812, width: 67"]Call Type[/TD]
[TD="class: xl2510812, width: 90"]Service Type[/TD]
[TD="class: xl2510812, width: 64"][/TD]
[TD="class: xl2610812, width: 234, colspan: 3, align: center"]Result[/TD]
[TD="class: xl1510812, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]111112[/TD]
[TD="class: xl1510812"]Outgoing[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl2410812"]Customer[/TD]
[TD="class: xl2410812"]Incoming Cal[/TD]
[TD="class: xl2410812"]Outgoing Cal[/TD]
[TD="class: xl2410812"]SMS[/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]98899[/TD]
[TD="class: xl1510812"]incoming[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812, align: right"]11112[/TD]
[TD="class: xl1510812, align: right"]1[/TD]
[TD="class: xl1510812, align: right"]2[/TD]
[TD="class: xl1510812, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]23332[/TD]
[TD="class: xl1510812"]Outgoing[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812, align: right"]7575[/TD]
[TD="class: xl1510812, align: right"]2[/TD]
[TD="class: xl1510812, align: right"]3[/TD]
[TD="class: xl1510812, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]98899[/TD]
[TD="class: xl1510812"]incoming[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]23332[/TD]
[TD="class: xl1510812"]Outgoing[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]7575[/TD]
[TD="class: xl1510812"]Outgoing[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]3457[/TD]
[TD="class: xl1510812"]incoming[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]7575[/TD]
[TD="class: xl1510812"]incoming[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]98899[/TD]
[TD="class: xl1510812"]Outgoing[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]7575[/TD]
[TD="class: xl1510812"]incoming[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]7575[/TD]
[TD="class: xl1510812"]Outgoing[/TD]
[TD="class: xl1510812"]Voice[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]7575[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"]sms[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]7575[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"]sms[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
[TR]
[TD="class: xl1510812, align: right"]7575[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"]sms[/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[TD="class: xl1510812"][/TD]
[/TR]
</tbody>[/TABLE]

however the list is of about 18000 records with 1800 unique records, and i want to show the number of times each unique record is called, calls or sms as you see in results of my example... Thanks all for the help and quick replies!
 
Upvote 0
What about this one...

Excel Workbook
ABCD
1Customer IDCall TypeService Type
21111OUTGOINGVOICE
32222INCOMINGVOICE
42222INCOMINGVOICE
52222SMS
61111INCOMINGVOICE
72222OUTGOINGVOICE
82222INCOMINGVOICE
92222SMS
101111OUTGOINGVOICE
111111SMS
122222INCOMINGVOICE
131111OUTGOINGVOICE
142222SMS
152222INCOMINGVOICE
161111OUTGOINGVOICE
172222INCOMINGVOICE
18
19Customer IDIncoming CallOut Going CallSMS
201111141
Sheet17
 
Upvote 0
To get unique IDs Paste this formula in A20 and press CTRL+SHIFT+ENTER not just enter

=INDEX($A$2:$A$18,MATCH(0,COUNTIF($A$19:A19,$A$2:$A$18),0))
 
Last edited:
Upvote 0
Another one... paste in A20

=IFERROR(INDEX($A$1:$A$20,MATCH(0,INDEX(COUNTIF($A$1:$A$20,"<"&$A$1:$A$20)-SUMPRODUCT(COUNTIF($A$1:$A$20,"="&A$19:A19)),),0)),"")

PRESS CTRL+SHIFT+ENTER
 
Upvote 0
PROFICIENT, I have tried to apply the above mentioned formulas but I get error, in the count formula it gives error on B$17 and Unique Id also giving me errors. please if possible would you like to email me the excel file in 97-2003 or .xls extension alongwith the formulas that you have applied? Please!
my email ID naar04@yahoo.com
waiting for your response!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Just using excel formulas, finding out unique RMs in a group id
Replies
9
Views
237
etaf
etaf
N
  • Question Question
Data Transformation using VBA like subtotal
Replies
1
Views
164
Domenic
D
G
  • Question Question
Formula to unique count with criteria based on a given year
Replies
8
Views
675
goethe168
G
T
  • Solved
Summarize cost data with spilled formula
Replies
3
Views
98
tana
T
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
339
krunal123
K

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top