Formula for Monthly Journal

[Safa]

I have SHEET 1 in excel. I call it DAY BOOK.
The day book I enter daily basis for my expenses and incoming fund.

I have SHEET 2 in excel. I call it JOURNAL.
I want to extract from SHEET 1 separate items (e.g. : transportation separate,
stationery separate, food separate etc...). Each Item's total I need total also
as shown in my table. This way I need automatic in SHEET 2.
How to give formula for this? Please comment.
SHEET 1

DATE	PARTICULARS	INCOME RS.	OUTGOING RS
8-1-2020	TRANSPORTATION​		1500​
8-1-2020	STATIONERY​	​	1200​
9-1-2020	FOOD​	​	800​
9-1-2020	FOOD​	​	1600​
9-1-2020	PARTY​	​	1700​
10-1-2020	CHEQUE NO.1245​	10000​	​
10-1-2020	STATIONERY​	​	500​
10-1-2020	COURRIER SERVICES​	​	5000​
11-1-2020	TOUR​	​	3000​
12-1-2020	CHEQUE NO.18554​	4500​	​
13-1-2020	PARTY​	​	2000​
13-1-2020	COURRIER SERVICES​	​	2000​
14-1-2020	COURRIER SERVICE​	​	5000​

SHEET 2 for monthly expenses

PARTICULARS	AMOUNT
TRANSPORTATION​	1500​
STATIONERY​	1700​
FOOD​	2400​
PARTY​	3700​
COURRIER SERVICE​	12000​
​

 

[edvrdsdvskj]

Hi Safa,

1. You should type manually
2. Is depending on where your data is put on. To help you please share copy of your file or use XL2BB tool (video instruction

)

i have doubts about this.
I am not a knowlegable person in formulas. Just trying to be.
Once I create formula there will be error from my end. I dont know
how to find out the error. But trying to find out hardly.
You showed me example formula in Sheet 2 - JOURNAL

Sheet 2 - JOURNAL

What about PARTICULARS field? Details of this shall I type manually or
autmatically will display all datas in PARTICULARS ?
Shall I give the formula in colum B or C ?
I have put this formula in B2 AND C2.
No effect at all.
=SUMIF('DAY BOOK'!B:B;JOURNAL!A2;'DAY BOOK'!D:D) I made this formula.
I dont know how to post my created table of JOURNAL here.
Please comment.

 

[Safa]

This one is not working. Please comment.

 

[Safa]

PARTICULARS	AMOUNT	FORMULA
		=SUMIF('DAYBOOK'!B:B;JOURNAL!A2;'DAYBOOK'!D:D)

 

[StuLux]

You should definitely investigate the use of Pivot Tables for your solution as this will highlight things like Typos etc. I copied your data and then added a pivot table and get the below. You can see from this that you have entered two versions of 'Courier Service(s)' and this is what Misca was pointing out to you as a reason to use Pivot Tables. By using Pivot Tables you don;t need to understand any formulas and, if you do as Misca advised (i.e. ensure your range is defined as a table) then the Pivot Table will continue to work as new rows are added. The Pivot Table will also give you additional functionality such as grouping the values by month etc.

Book1
	A	B
3	Row Labels	Sum of OUTGOING
4	COURRIER SERVICE	5000
5	COURRIER SERVICES	7000
6	FOOD	2400
7	PARTY	3700
8	STATIONERY	1700
9	TOUR	3000
10	TRANSPORTATION	1500
11	Grand Total	24300
Sheet5

 

[Safa]

In row 4 and 5 there is repeatedly courrier service. I need those two rows totalling together and one courrier service row. For example : transportation in 5 rows. I need it only in one row totalling all 5 rows. Just show me how to do that
in pivot table. Can I put source data in sheet 1 and target data in sheet 2 ? Just show me in pivot table itself.

 

[Safa]

I want to change the data source in pivot table. For example : I want to add
some more rows in the existing table.
I use msexcel 2016 version.

 

[StuLux]

Do some research in to Pivot Tables and get an understanding for how they work as it will resolve your problem, there is plenty of material either in this forum or other websites that will teach you pivot table basics. The reason that you have Courier services listed twice is that in your data you have an entry against 'Courier Service' and a further two entries against 'Courier Services' (i.e. with an S on the end). Pivot tables will total entries that are the same but will separate for any differences.

 

[Safa]

If you want to use the formulas you have to type the possible values manually. And keep adding them every time you enter new values that weren't found in your data before. And if you happen to have typos in your data you'll have to find / correct them manually unless or you'll never know your journal may be missing a couple of things.

Pivot tables would take care of all that for you + you'll never have to worry about formula errors either: Build it once, press the Refresh All button every time your data has changed and that's it.

 

[Safa]

I got almost about pivot table and experimented. Your replies are great.

 

[Safa]

I need clear cut idea about sumif also please.