Excel for summarizing/arranging data

[naar]

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

 

[Al Chara]

You could use something simple like SUMIF or use a PivotTable to summarize the table.

 

[naar]

You could use something simple like SUMIF or use a PivotTable to summarize the table.

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

 

[proficient]

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

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


Sheet3

*	A	B	C	D	E
1	Customer ID	Incoming Calls	Out Going Calls	SMS	Total
2	1111	16	12	82	110
3	*	*	*	*	*
4	*	*	*	*	*
5	Customer ID	Incoming Calls	Out Going Calls	SMS	Total
6	1111	5	6	25	36
7	1212	4	5	36	45
8	1111	6	4	45	55
9	1255	2	7	21	30
10	1212	1	1	45	47
11	1111	5	2	12	19
12	1212	4	0	54	58

<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>
</tbody>

Spreadsheet Formulas

Cell Formula 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

 

[Al Chara]

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

Please provide a larger sample of how your data is organized (no sensitive information).

 

[naar]

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


Sheet3

*	A	B	C	D	E
1	Customer ID	Incoming Calls	Out Going Calls	SMS	Total
2	1111	16	12	82	110
3	*	*	*	*	*
4	*	*	*	*	*
5	Customer ID	Incoming Calls	Out Going Calls	SMS	Total
6	1111	5	6	25	36
7	1212	4	5	36	45
8	1111	6	4	45	55
9	1255	2	7	21	30
10	1212	1	1	45	47
11	1111	5	2	12	19
12	1212	4	0	54	58

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula 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

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

Customer ID	Call Type	Service Type		Result
111112	Outgoing	Voice		Customer	Incoming Cal	Outgoing Cal	SMS
98899	incoming	Voice		11112	1	2	3
23332	Outgoing	Voice		7575	2	3	1
98899	incoming	Voice
23332	Outgoing	Voice
7575	Outgoing	Voice
3457	incoming	Voice
7575	incoming	Voice
98899	Outgoing	Voice
7575	incoming	Voice
7575	Outgoing	Voice
7575		sms
7575		sms
7575		sms

<tbody>
</tbody>

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!

 

[proficient]

What about this one...

Excel Workbook
	A	B	C	D
1	Customer ID	Call Type	Service Type
2	1111	OUTGOING	VOICE
3	2222	INCOMING	VOICE
4	2222	INCOMING	VOICE
5	2222		SMS
6	1111	INCOMING	VOICE
7	2222	OUTGOING	VOICE
8	2222	INCOMING	VOICE
9	2222		SMS
10	1111	OUTGOING	VOICE
11	1111		SMS
12	2222	INCOMING	VOICE
13	1111	OUTGOING	VOICE
14	2222		SMS
15	2222	INCOMING	VOICE
16	1111	OUTGOING	VOICE
17	2222	INCOMING	VOICE
18
19	Customer ID	Incoming Call	Out Going Call	SMS
20	1111	1	4	1
Sheet17

 

[proficient]

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

 

[proficient]

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

 

[naar]

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!