[SOLVED] Excel / Spreadsheet newbie questions

[brannsiu]

Please look at the picture of example below

This is only a simplified example, in fact, I have many months and years recorded in my spreadsheet file, however, I'd like to create a sum of income and expense of EACH month individually, do I need to do it one by one or is there any more automated way to make this?

In fact, at the moment, when I want to check the total income or expense in a months, I need to manually select that particular month, sometimes there could be up to tens or hundreds of rows to select, is there any automated way?

 

[Lafong]

Look at December.

The sum of December income is 700

The sum of December expense is 300.

Imagine the cell in which you want to put the formula.

Do you want it to read 700, 300, or 1000?

You say income AND expense, so I'm unclear.

"Automated"?? Are you unwilling to manually drag a formula down one row as necessary?

Are you familiar with Autosum in Excel?

Clarify if you can.

 

[sizzling]

Make sure Excel is treating the date column as ‘date’ data. Then just use a pivot table, it should auto group by month. Once you know how it should take seconds.

 

[brannsiu]

Look at December.

The sum of December income is 700

The sum of December expense is 300.

Imagine the cell in which you want to put the formula.

Do you want it to read 700, 300, or 1000?

You say income AND expense, so I'm unclear.

"Automated"?? Are you unwilling to manually drag a formula down one row as necessary?

Are you familiar with Autosum in Excel?

Clarify if you can.

Sorry, not AND. Ok let me be more clear
The picture is only an example for illustrating my questions.
In fact I have more than 4 years things recorded that is about hundreds or thousands of rows.

Now the biggest mess is when I want to find out the total income OR total expense of a particular month I have to manually mouse click and select all rows of the month, it is really troublesome.

Any way to make it easier?


Autosum? Did you mean =SUM( , ). In fact it is not necessary because once I mouse select the rows there will be a SUM shown at the bottom immediately... Now my problem is as said above.

 

[brannsiu]

Make sure Excel is treating the date column as ‘date’ data. Then just use a pivot table, it should auto group by month. Once you know how it should take seconds.

Yes, the date column is formatted as "date", rather than text. Pivot table??

 

[kanewolf]

Yes, the date column is formatted as "date", rather than text. Pivot table??

Yep pivot table. A capability in Excel to create a new sheet summarizing other data.
Lots of articles on pivot tables. You would group by month and use sum or subtotal to get your values.

 

[USAFRet]

=SUMIF
https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b

Thusly....
Col A is the Date, Col B is the Amount, Col C is the numeric Month from Col A.

=SUMIF(C:C, 11, B:B)

If desired, you could add in the Month function to reference the Month value directly.
https://support.microsoft.com/en-us/office/month-function-579a2881-199b-48b2-ab90-ddba0eba86e8

 

[Lafong]

I may not be understanding completely, but it sounds to me like you could use the sum formula.

I'd think that for December, you could put that formula in the next column to the right of "200". That cell could sum either the income column for December or the expense column for December.

Something like:

=SUM(F1:F26888)

 

[sizzling]

Yes, the date column is formatted as "date", rather than text. Pivot table??

Google Excel pivot table. For this sort of thing it’s ideal, very easy with a little practice and there are loads of YouTube guides.