Home > Forum Home > Analyzing Corporate Financial Data > Excel Spreadsheet Tracking Yearly Accounting, Metadata, Formulas Share

Excel Spreadsheet Tracking Yearly Accounting, Metadata, Formulas

Excel Help for Excel Spreadsheet Tracking Yearly Accounting, Metadata, Formulas in Analyzing Corporate Financial Data


Forum TopicPost Reply Login

Excel Spreadsheet Tracking Yearly Accounting, Metadata, Formulas

Rate this:
(4/5 from 2 votes)
Confused I hope everyone is healthy and doing well, I have been tracking my accounting invoices/payments into an excel spreadsheet up until recently using Excel 2003 entering all the raw data and manually doing the totals, I just got Excel 2021 and would like to automate the process as best as possible and would appreciate anyone who could assist.

My header columns are as follows;

BANK DATE - INVOICE DATE - CHEQUE DATE - INVOICE # - CUSTOMER / SUPPLIER - CATEGORY - PRODUCT / DESCRIPTION - QUANTITY - $PRICE PER UNIT - $SUBTOTAL - $SHIPPING - $FEE EBAY - $FEE PAYPAL - $GST - $HST - $PST - $TOTAL - CURRENCY - BANK ACCOUNT - BANK BALANCE

I would like to add formulas to the spreadsheet that will generate monthly & yearly totals based on CATEGORY and other columns like CUSTOMER / SUPPLIER and PRODUCT / DESCRIPTION.

I really appreciate any help that would make my spreadsheet far more automated/useful for tracking then it currently is very tedious/manual.

Thanks!
 Posted by on
 
Replies - Displaying 1 to 3 of 3Order Replies By: Most Recent | Chronological | Highest Rated
Confused
Pivot table with group by date
Rate this:
(3/5 from 1 vote)
I would first try making a pivot table from the data and use the grouping in the date rows to get totals by month and year
 Excel Business Forums Administrator
 Posted by on
Confused
Sample spreadsheet attached
Rate this:
(3/5 from 1 vote)
https://filebin.net/6u78e9gkkxa39o1j

Attached is a very condensed sample of the data I'm trying to sort with formulas, the main priority is to be able to AUTOSUM rows based on the "BANK ACCOUNT" "CURRENCY" "BANK DATE" "INVOICE DATE" and "CATEGORY", providing summaries monthly maybe placed on a separate worksheet. I did some searching on google I expect thousands of people have spent a long time making very complex accounting templates but so many results I was hoping to get some suggestions/help my best plan from the experts. Thanks again!
 Posted by on
Confused
SUMIFS formula
Rate this:
(3/5 from 1 vote)
If you know which items need to be summed, you can use the SUMIFS function which will take multiple columns and criteria to sum only matching rows from a value column.
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Excel templates and solutions matched for Excel Spreadsheet Tracking Yearly Accounting, Metadata, Formulas:

Categories: Navigation and Auditing

© Business Spreadsheets   Legal   Follow us: