[Solved] Adding data from 2 months

[Pinkmen]

Hello there! Yet again with another problem..

This time I need to add a range of data that spans through two seperate months.

In simple words how do i add the data from september 5 through october 10?

Thx!

[Hagar Delest]

With so little information, that's quite hard to tell.
Please upload a sample file or give an example of how the data are put.

[Villeroy]

To enter a new record into a rectangle of cells depicting a table, you insert a row of new cells. This forces all formula cells to update their references. For instance, a formula like =SUM(A1:A99) updates to =SUM(A1:A100) after insertion of a new row.
Then you enter the date in the column having dates only.
Enter numbers and text values in other columns where the respective category of data belongs to.

Code: Select all

Date		Outlet	Staff	Sales
----------------------------------------------------
2024-09-05	West	Smith	765,87 €

Stubbornly one record below the other, a date, 2 strings and a number.


How to enter a date into a Excel-like spreadsheet:
5/ enters this month's 5 day.
5/2/ enters this year's 5th of February (2nd of May in US context)
5/2/23 enters 5th of February (2nd of May in US context) of the year 2023. Two-digit years between 0 and 29 belong to this century. Better use 4-digit years when necessary.

[Pinkmen]

Further information on what it is i am trying to learn.

I have a data sheet that has these column names.

|Date  |Amount|Currency|Category|Month - Year|Expense or Income| 
|9/1/24| $5.00|Debit   |Gas     |    9 - 2024|Expense          |
|9/4/24|  $100|Debit   |Deposit |    9 - 2024|Income           |

And so on and so on. I have a ton of data in here for months and months. What I need to do is make a statement from month and day 8/15 Through 9/17.

Basically how do I =sumproduct(Amount; Month(8)day(15):Month(9)Day(17);

Not sure if i explained it too well. but thats the gist of it.

[Villeroy]

You don't need to enter the "Month - Year" because it can be derived from the date. The information is contained in the date already.
A pivot table is the easiest method to aggregate such tables by month, year, quarter, day, category etc.

[Pinkmen]

I see! Thanks alot!

[MrProgrammer]

In simple words how do i add the data from september 5 through october 10?

[Tutorial] The SUMPRODUCT function examples X03, X04, X12, and X35

If you need any additional assistance attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach a spreadsheet file.

[Villeroy]

I see! Thanks alot!

Open my sample file and create another pivot table from the same source table.
1. Click any single cell in the source table and choose menu:Data>Pivot Table>Create...
2. The selection expands to the current region of adjacent, non-empty cells. Confirm that you want to create a pivot table from the current selection.
3. In the following dialog:
3.1. Drag [Date] to [Row Fields]
3.2. Drag [Product] to [Row Fields]
3.3. Drag [Person] to [Column Fields]
3.4. Drag [Value] to [Data Fields]
4. Click [More] and choose "Results to:" <-new sheet->
5. Click [OK]
Now you have another pivot table on a new sheet calculating the values for each person and product at each day.
6. Format the day numbers in the first column as date. Any date format will do.
7. Click any single cell in the date column.
8. Call menu:Group&Outline>Group (key F12), check "Month" and "Year" and confirm [OK].

Now you have the same pivot table that I created directly besides the source table.
Data>Pivot>Refresh (or right-click>Refresh) refreshes the whole thing after the source table has been modified.
Data>Pivot>Delete deletes the whole thing without affecting any of your precious data.
Data>Pivot>Create lets you add more fields and play with several more options without affecting any of your precious data.
You can drag around the gray boxes on sheet.
You can use various filter options with the gray boxes.