You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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.


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. I made 4 sheets: (1) revenues, (2) expenses by month (screenshot #1), (3) profit, and (4) business expenses by category (screenshot #2).


For 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. Advertising, Supplies, Telephone and Utilities. 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.


Again, the first screenshot is my business expenses for May. The second screenshot has the categories from the drop-down menu of type of expense. 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.




iMac Pro

Posted on May 8, 2022 1:43 PM

Reply
Question marked as Top-ranking reply

Posted on May 8, 2022 3:04 PM

I would go with SUMIFS.


Here the formula based on your example.

The sum will be calculated based on the amount that is in column D, the test will be done based on type in column C and the condition is in the second chart in column A. This formula is in the second chart (Output) in column B (B2, ..., Bx)


Hope that will help you with your problem.


Ralf

Similar questions

9 replies
Question marked as Top-ranking reply

May 8, 2022 3:04 PM in response to dickstes

I would go with SUMIFS.


Here the formula based on your example.

The sum will be calculated based on the amount that is in column D, the test will be done based on type in column C and the condition is in the second chart in column A. This formula is in the second chart (Output) in column B (B2, ..., Bx)


Hope that will help you with your problem.


Ralf

May 8, 2022 3:19 PM in response to Ralf-F

That's awesome! That's exactly what I want - those sums! Now for me to copy that formula...


I figured out how to get to the SUMIFS function (pressed = key, typed simfs, pressed enter key) - screenshot #1, but having trouble filling it out (screenshot #2).


How exactly do I click to fill out that formula? It seems to jump around, delete parts of the formula, change colours, etc... Please provide step-by-step of exactly where to click and how.





May 8, 2022 3:36 PM in response to dickstes

Step 1: Select sum-values


Step 2: Select column D (Letter of the column) or the column with your amount for tax


Then it should look like this


Repeat for test-values and select column C


For the condition you must first select condition and then the cell A? with the right type in your second chart, where the results should be calculated.


Ralf

May 9, 2022 12:01 AM in response to Ralf-F

If you have a recent version of Numbers don't slave away trying to get formulas to work for summaries like this! Take advantage of powerful built-in functionality and get your answer in seconds.


Keep your multi-month data in a single table something like this:




Then, when you want a report by category simply click in the table and in the menu go to Organize > Create Pivot Table and choose whether you want the summary report on the same sheet or a separate sheet. Then drag in the Pivot Options pane like this:




All done in a couple of seconds! Less than the time it takes to start typing a formula, let alone debug one.


Whenever you add or change data in the original data table you can easily update the Pivot Table too by clicking it and going to Organize > Refresh Pivot Table in the menu.


You can have multiple Pivot Tables based on the same data table to give you different views of your data (e.g., you could see it month by month, etc.). Just click the data table and start a new one.


Check out the 'Pivot Table Basics' template at File > New in the menu for working examples.


SG

How to Sum by Type of Expenses

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.