SUMIFS(Items::$B,Items::$A,">="&B$1,Items::$A,"="&B$1, The date in column A is on or after the date in the header cell of 'this column', Each cell contains a full Date and Time value, but is formatted to display only the short monthname and the day nuber (1).Įach of the body cells contains the formula below, entered in B2, then filled to the end of row 2and down to the last row of the table. Row 1 contains the first of the month date for each month to be summarised. As noted above, these must be an exact match to the categories entered in the Items table. Set the first one to "none", then use the Fill button (small yellow circle that appears on the bottom edge of the cell when you bring the pointer close to it) the drag a copy into all the cells in that column.Ĭolumn A contains the list of categories to be summarised. Select the cells now containing category names, then set the Data Format for the selected cells to Pop-up menu, and set them to "Start with blank"Įach of those cells then contains a pop-up menu, all with the same choices, and all showing the same value as was originally pasted into that cell. The easiest way to ensure this is to make the list in Column A of Summary, copy that list, then select cell D2 (one click) and use Edit > Paste and match Style to Paste the list into column D of Items. The column (or columns) does not enter int the formulas.Ĭategory names in column D must match the names in the list of categories in column A of the Summary table. I left formatting at 'automatic', you may want to use 'number' with 2 decimal places or 'currency' with two decimal places.Ĭolumn C represents additional columns used the add details. If the date is entered as shown, Numbers automatically appends the current year to the Date part, and adds the Time part, set to 00:00:00 (midnight, at the beginning of the day.Īmounts in column B are entered from the keyboard. ALL 'dates' in Numbers are Date and time values. The summary is constructed using the formula shown below the tables (and copied below, with notes).ĭates in column A are entered as the items are recorded. If the tables have distinct names, not shared with any other table in the document, then the Sheet names are not needed in the formulas (and Numbers will drop them if you include them). I'd suggest doing the same while setting them up, then changing the names to what you want once you have the formulas up and running. I used short names to keep the formulas short. I want it to grab the two "Telephone and Utilities" amounts from sheet #2 (Bell and Shaw) where I filled it in by date and have the sum show up here on sheet #4, so I can just copy these amounts onto my tax return. The second screenshot has the categories from the drop-down menu of type of expense. On the "business expenses by category" sheet (aka Sheet #4/screenshot #2), I want it to add up the items chosen from the drop-down menu in that "Type" column.Īgain, the first screenshot is my business expenses for May. Advertising, Supplies, Telephone and Utilities. I made 4 sheets: (1) revenues, (2) expenses by month (screenshot #1), (3) profit, and (4) business expenses by category (screenshot #2).įor each expense on the "expenses by month" sheet (aka Sheet #2/screenshot #1), I made a "Type" column (3rd column in screenshot #1) whereby I used the exact "business expense" name that we need for our tax return in Canada, e.g. I made a "revenues and expenses" document that I want to use to keep track of my sales and expenses, as well as use to fill out my taxes in Canada every year. How to Sum by Type of Expenses Please note that I rarely use spreadsheets (total beginner), so please be simplistic and step-by-step in your answers.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |