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

 

[proficient]

are you using excel 2003...?

 

[proficient]

use SUMPRODUCT rather than COUNTIFS

IN B20
=SUMPRODUCT(($A$2:$A$17=$A20)*($B$2:$B$17="INCOMING"))

IN C20
=SUMPRODUCT(($A$2:$A$17=$A20)*($B$2:$B$17="OUTGOING"))

IN A20
=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)) (THIS IS AN ARRAY FORMULA SO FIRST PRESS AND HOLD CTRL SHIFT PRESS ENTER

 

[naar]

Kamran Mumtaz Thank you so much, this solves my problem. one question more COUNTIFS formula excel 2003 me use ni hota? online koi aisi book ya guide reference jis me functions or unka use diya hua ho plz?

 

[proficient]

Kamran Mumtaz Thank you so much, this solves my problem. one question more COUNTIFS formula excel 2003 me use ni hota? online koi aisi book ya guide reference jis me functions or unka use diya hua ho plz?

Hhmmm... Could you please post your question then I'll try to find out the way... may be the SUMPRODUCT function can do this...

 

[proficient]

here is some useful website links for excel...

Contextures Excel Tips, Excel Tutorials and Excel Examples
Get Digital Help - Excel formulas, array formulas, vba, charts, pivot tables, templates and much more.
Welcome to Chandoo.org - Learn Excel, Charting Online

 

[naar]

here is some useful website links for excel...

Contextures Excel Tips, Excel Tutorials and Excel Examples
Get Digital Help - Excel formulas, array formulas, vba, charts, pivot tables, templates and much more.
Welcome to Chandoo.org - Learn Excel, Charting Online

How to filter / skip certain ids starting with certain numbers for example what if i want to skip all ids starting with 92 the number might be 923338884756... Please!

 

[naar]

How to filter / skip certain ids starting with certain numbers for example what if i want to skip all ids starting with 92 the number might be 923338884756... Please!
Thanks for all your help and prompt response


imagine there is a list of numbers


92303474564
92347656864
92364585684
93378437666
91278468656
91376486578
917847628765
92303474564
92347656864
92364585684
93378437666
91278468656
91376486578
917847628765
44565667575
54787687687
7656756656




While getting Unique IDs as posted in the form you quoted an array function. but while getting unique ids it is required that unique id list doesnt include homecountry numbers in this example all numbers starting with "92"