Help with Formula for Numbers

Question

Level 1

4 points

Help with Formula for Numbers

So I have a spreadsheet that I use to track expenses for a rental property that we have. One page is a running ledger of expenses. For example, lets say On one sheet of the file I have the following fields (Date, Description of Purhcase, Category, & Cost). On another sheet is more of a "YEAR AT A GLANCE" type thing where I have every month as the column headers, and then the rows are essentially the categories (Insurrance, Supplies & Tools, Furniture, etc). What I would like to know is if there is a way to create a formula that would grab an expense from page 1 based on it's Category and then place it in the appropriate category total box on the following page and then sum them together so that I don't have to add all of those respective expenses up at the end of each month. Thanks in advance for helping show me new tricks.

Posted on Oct 27, 2021 6:24 PM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

21,945 points

Oct 28, 2021 7:50 AM in response to happym12

Hi happym12,

Adding to Barry's excellent solution, here is another way using formulas.

Formula in the table Expense Ledger B2: IFERROR(MONTHNAME(MONTH(A2)),"")

Fill down.

You can hide column B when all is working well.

The IFERROR function inserts "" (NULL) if there is no date in a cell in column A (to avoid those red error triangles).

The table Summary by month Column A is formatted as Text.

Formula in Summary by month B2: SUMIFS(Expense Ledger::$E,Expense Ledger::$B,$A2,Expense Ledger::$D,B$1)

Fill down and fill right.

Happy numbering!

Regards,

Ian.

Reply

Answer 2

SGIII

Level 8

37,562 points

Oct 28, 2021 1:32 PM in response to happym12

Highly recommend looking into Pivot tables (at Organize > Create Pivot Table in the menu) for problems like this.

They're fast, flexible, and don't require formulas or extra columns. I set this up in less than a minute, with a few clicks and drags.

SG

Reply

Answer 3

Yellowbox

Level 7

21,945 points

Nov 11, 2021 6:51 AM in response to happym12

Hi happym12,

It is not a good idea to expose email addresses in this public forum with millions of users.

Perhaps you could post screen shots of your Cash Ledger, credit card ledger and summary by month.

By the way, (Numbers terminology) they are not separate spreadsheets; they are tables on separate Sheets (Tabs) within one Numbers document 😉.

happym12 wrote:

Some things are paid with CC and some with cash and some with both.

Hint: it will be more efficient to have all payments in one table, with a column to identify cash or credit card, then use SUMIFS to "pull" relevant payments into a summary table.

Regards,

Ian.

Reply

Answer 4

happym12 Author

Level 1

4 points

Nov 6, 2021 4:51 PM in response to Yellowbox

Thank you all so much for the amazing responses. I’ve been able to get some of it to work but still having some issues. I’m afraid I simplified my scenario too much, we’re really close but another level that I have added to it might be messing it up. Would you mind emailing me? I could shoot you my file so you could see how it’s setup and then see if you could help me figure out why it’s not computing properly?

Reply

Answer 5

happym12 Author

Level 1

4 points

Nov 8, 2021 7:47 PM in response to Barry

So I was finally able to figure out how to get your solution to work. So, another level of complication to this is that I technically have three separate spreadsheets. One is a Cash Ledger, the second is the credit card ledger and the third is the summary by month. Is there a way to have the summary by month do the same thing that you suggested but after searching both the cash ledger and cc ledger? Some things are paid with CC and some with cash and some with both.

Reply

Answer 6

happym12 Author

Level 1

4 points

Nov 11, 2021 7:00 AM in response to Yellowbox

I think maybe I assumed you were like me and had alias email addresses you could use for purposes such as this. Unfortunate there’s not a direct message feature within the threads. I just didn’t want to bother everyone already in the thread for something that could be more focused. Secondly, thanks for the tips about the terminology. I’m sure it’s not the first time I’ve made a mistake with that and definitely won’t be the last but also would assume you would get the basis of my question otherwise. Sounds like at this point I’d just be as well off to figure this one by myself. Thanks to everyone in here who took the time to help a dunce like me.

Reply

Answer 7

Barry

Level 9

60,023 points

Oct 27, 2021 9:27 PM in response to happym12

Hi Hap',

There are two routes you can follow to do this—using a formula, or using the Categories feature of Numbers.

Here's an example, using the formula approach:

Expense Ledger is your Ledger table.

There are no formulas on this table.*

Column A contains the date of each transaction. This must be a Date and Time value, but is entered as 'date only'. Numbers automatically sets the time to 00:00:00 (midnight at the beginning of the date entered).

I've omitted the data in column B, as it does not enter into the calculations.

Each cell in column C contains a copy of a pop-up menu containing the names of each category you are using. The Pop-up menu is set to 'start with a blank', as can be seen in row 10.

Column C contains entered data, the amount of each expense transaction.

Summary by Month is your Summary table.

The table contains one formula,** entered in cell B2, and filled down and right into the rest of the cells shown. The formula is shown below the two tables.

The dates shown in row 1 are the first day of each month, and must be Date and Time values with the time part set to 00:00:00 (by Numbers) and not displayed, and the Date part set to the first day of the month in each column . Numbers is a bit picky in matching dates displayed in different formats, which is what led me to include the Day (01) in each display.

Formula in B2:

SUMIFS(Expense ledger::$D,Expense ledger::$A,">="&B$1,Expense ledger::$A,"<"&EDATE(B$1,1),Expense ledger::$C,$A2)

This is a SUMIFS formula which sums the values in column D of Expense Ledger, including only the values in cells on rows where all of the following are true:

- the value in column A is later or the same as the date in B1 of Summary…
- the value in column A is before the date one month after the date in B1…
- the category in column C matches the category in this row of column A (of Summary…)

Results are as shown on the table.

*I also used a formula to enter a series of random (but increasing) dates in column 1 of Expense Ledger, but this does not enter into the functioning of the table.

**Dates in cells to the right of B1 were calculated using the EDATE function shown in the formula. If you choose to use this function for this purpose, I'd suggest replacing the formulas with the calculated values before using the spreadsheet. there's no point in recalculating these formulas each time a change is made to the table—they are static values, changed only once a year.

Regards,

Barry

PS: Categories is not available on my older Mac, but I would expect to see a Categories solution offered here before long.

B.

Reply

Help with Formula for Numbers

Similar questions