Formula for Monthly Journal

Safa

New Member
Joined
Jan 9, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
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

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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Welcome to the board!

There are several formulas that could do what you're after such as SUMIF or SUMIFS. However, I'd recommend you'd create a pivot table from your data and use that as your Journal.

If you're not familiar with Pivot Tables you can find great tutorials from YouTube.

Also, do yourself a favor and make sure your data on the first sheet is in a Table (Insert => Table or Ctrl + "T" ). This way you don't have to change the ranges every time you add new rows to your data.

One thing, though: Formulas update automatically when your data changes. With Pivot Tables you need to refresh the data before the changes appear.
 
Upvote 0
Welcome to the board!

There are several formulas that could do what you're after such as SUMIF or SUMIFS. However, I'd recommend you'd create a pivot table from your data and use that as your Journal.

If you're not familiar with Pivot Tables you can find great tutorials from YouTube.

Also, do yourself a favor and make sure your data on the first sheet is in a Table (Insert => Table or Ctrl + "T" ). This way you don't have to change the ranges every time you add new rows to your data.

One thing, though: Formulas update automatically when your data changes. With Pivot Tables you need to refresh the data before the changes appear.
I dont know anything about pivot table. Can I learn this in simple and easy way please. I saw your youtube, but I cannot grasp it easily.
 
Upvote 0
0) Start by selecting a cell in your source data. If you haven't turned it into an Excel table yet, do it by pressing Ctrl + "T" (or click the Table icon on the Insert tab). Before selecting OK at the screen that appears make sure the "My data has column headers" is selected. This part is not necessary but it'll make things a lot easier for you in the long run when you add new rows to your data.
1) Select a cell in your data table and click the Pivot Table icon on the Insert tab. Excel should get your data range right so just click OK. A new sheet is created with an empty Pivot table on it. On the right side of the screen you should see the Pivot Table Fields pane with your column headers visible on the upper . Drag the fields you want to the right boxes below:
 
Upvote 0
0) Start by selecting a cell in your source data. If you haven't turned it into an Excel table yet, do it by pressing Ctrl + "T" (or click the Table icon on the Insert tab). Before selecting OK at the screen that appears make sure the "My data has column headers" is selected. This part is not necessary but it'll make things a lot easier for you in the long run when you add new rows to your data.
1) Select a cell in your data table and click the Pivot Table icon on the Insert tab. Excel should get your data range right so just click OK. A new sheet is created with an empty Pivot table on it. On the right side of the screen you should see the Pivot Table Fields pane with your column headers visible on the upper area. Drag the fields you want to the right boxes below: Whatever you drag to the "Rows" area becomes the left column on your Pivot table. "Columns" is the "header row" of your values and "Values" is the numeric values you want to do calculations with. The "Values" is basically the only field you must have. Everything else is optional. You can also have more than one field in each area but the more there's elements on your pivot table, the harder it becomes to understand what you're actually looking for: Monthly Sales of Products is quite easy to understand - especially if you filter the year to show only one year - but if you add colors and customer segments and their locations as well as average sales and number of purchases on the same table it becomes a mess. You'll still get the correct numbers but it'll be a lot harder to understand all the fields a number is filtered by.
2) The default calculation for numeric values is SUM. If you drag text values to the Values field you'll get COUNT (of rows) by default. Right click a number in your Values field of the Pivot table (not the PT Fields pane on the right but the actual pivot table) and choose the "Summarize values by" to change the calculations of your numeric values. The same right click can be used to set Number Formatting to your value fields and if you're not happy with the basic calculations go ahead a check what you'll find under the "Show values as" option.
3) When you add new rows to your data and want to show those in your pivot table you'll have to refresh to pivot cache. If you right click any cell in your pivot table you can see the Refresh option there but the easiest way might be clicking the "Refresh All" button found in the "Data" tab of your Excel Ribbon. That way you'll refresh everything at once.
 
Upvote 0
1.jpg
 
Upvote 0
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
2.jpg


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.
 
Upvote 0
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.
 
Upvote 0
Hi Misca. Sorry to tell you that I did not follow you.
I can understand only from example. Would appreciate your
favor on this in reply. Show me this through a simple example please.
After inserting formula I could not find any amount in colum.
How is it working.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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