Formula for Monthly Journal

Safa

New Member
Joined
Jan 9, 2020
Messages
14
Office Version
2016
Platform
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​
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,558
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.
 

Safa

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

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,558
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:
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,558
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.
 

Safa

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

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,558
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

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

Forum statistics

Threads
1,081,493
Messages
5,359,037
Members
400,516
Latest member
saesa

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top