How to extract info based on any defined category

Dear Techies,

I am a newbie to excel so please excuse for my foolishness. I have an excel file in which i store my daily accounts (just income and expense) of very small business. Now i want to categorize and see the expenses by the end of the month or week (as i wish). for eg; the content of my excel sheet are;

Opening Balance 1000

Date; Name-of-Payee; Payment-Details; Received; Payment; Balance;
1/11/11; Cremo; Petrol ; ; ; 100; 900;
1/11/11; Cremo; Mobile Bill; ; 300; 600;
2/11/11; ; Sale of Goods; 150; ; 750;
2/11/11; Cremo; Carpenter; ; 200; 550;
3/11/11; ; Sale of Goods; 100 ; ; 650;
4/11/11; Cremo ; Petrol; ; 100; 550;
5/11/11; Cremo; Internet Bill; ; 200; 350;
6/11/11; ; Sale of Goods; 500; ; 850;
7/11/11; Cremo; Raw materials; ; 350; 500;
8/11/11; ; Sale of Goods; 200; ; 700;
9/11/11; Cremo; Raw materials; ; 200; 500;
10/11/11; Cremo; Petrol; ; 150; 350;


now i have to see in the end like;
Petrol; 350;
Raw Materials; 550;
Communication; 500;
Total Sale; ; 950;

etc.


how can i extract this info from the above text which i already entered to my excel file....

PLEASE HELP..... I am searching for a way around for this for the last entire week...

I also get in to Pivot tables. But i am not able to understand how to use it in my scenario. Regarding the Relation of Comminication, Internet Bill and Moile Bill we can add a category column and mention a code for each category and mention the code when we are adding the entry, for eg;

Date; Name-of-Payee; Payment-Details; Received; Payment; Balance; Category-Code
1/11/11; Cremo; Mobile Bill; ; 300; 600;101
5/11/11; Cremo; Internet Bill; ; 200; 350;101

here the category code "101" is identical for Internet Bill and Mobile Bill...

But i dont know where or how to define these codes in excel and how can i manipulate it to get my result...

PLS ADVICE...

Originally Posted by khamilton

Give it a shot!!!

Attachment 129000

Yes it is working! But i cant understand how can i use it... i mean the formula which u used in it...

my excel sheet will have around 150 entries... so where can i place this, how to put the formula, i am lil confused...

But i love to see it is working...

Thx a lot khamilton....

Could you pls explain it to me... or if you have any easy method by categorizing, then pls advice...

Originally Posted by khamilton

Maybe this will help you a little more.

Attachment 129002

This one is really better dear...

Now one more thing. Could u pls advice, how can i simply edit this fomula, when i am applying in my monthly account sheet, where the number of entries will be varying in between 100 to 200 lines.

Is it like editing the complete $C$3:$C$end of the coloumn and the same way to $E and $D

=IF(SUMIF($C$3:$C$14,I3,$E$3:$E$14)=0,SUMIF($C$3:$C$14,I3,$D$3:$D$14),SUMIF($C$3:$C$14,I3,$E$3:$E$14))

or it there any simplest method?

Once again thanking you dear for your GREAT solution.

Originally Posted by khamilton

Maybe this will help you a little more.

Attachment 129002

Dear Khamilton....

I feel so happy after applying your formula in my sheet.... REALLLY REALLLY REALLLYYYY BIIIIIGGGGGGGGGGGGGGGG THHHHHHHAAAAAAAAAANNNNKKKKSSSSS DEARRRR

THANK YOU VERY MUCH!!!!

In my actual excel sheet, i made a section called "Categorized Expenses" and i manually edited the expense categories like "Petrol" Mobile Bill" , etc, then in the next coloumn i copied your formula and edited according to my excel sheets coloumns and rows...

the result was AAMMMMAAAAZZZZZIIIIIINNNNNGGGGG Dear...

now i can see expenses of each category easily....


After i finished this think, i saw that in your example file, you didnt typed "Petrol" in the "Unique entries from coloumn C" , rather it was also a formula.

but when i tried to apply that formula, it didnt worked for me... i tried with my actual excel sheet as well as in your example sheet itself, but in both case it is not working... i am attaching that sample which you gave me, with my trying in automated categorization. PLease check and advice how to achieve that too...


Once again thanking you....

Originally Posted by creamecake

Dear Khamilton....

I feel so happy after applying your formula in my sheet.... REALLLY REALLLY REALLLYYYY BIIIIIGGGGGGGGGGGGGGGG THHHHHHHAAAAAAAAAANNNNKKKKSSSSS DEARRRR

THANK YOU VERY MUCH!!!!

Khamilton...... Awaiting your response....