1

So, I'm making a personal budget table that is similar to YNAB in functionality. I want to use Numbers but I can easily switch to Excel if this can't be achieved.

I have two sheets; Budget and Transactions.

In Transactions, I have a list of bank transactions with a category (matching a list of categories in the Budget sheet) and their in/out values. I need to sum all in/out values that are in the same row as any matching category on the budget page. For example;

Budget Sheet

    A              B         C        D
1 Categories   Budgeted    Spent    Remaining           
2 CategoryA      100        <?>     SUM(Budgeted, Spent)
3 CategoryB      250        <?>     SUM(Budgeted, Spent)

Transactions Sheet

   A            B           C            D      E
1 Date      Category    Description     Out     In
2 1/1/2017  CategoryA   cat-a desc.     57
3 1/2/2017  CategoryB   cat-b desc.             15
  • I don't know Numbers, but in excel this should be easy. But you need to add a bit more detail. – Yisroel Tech Jan 17 '17 at 1:30
  • What info is missing? (to better explain I've added Rows and Columns). I need to come up with a formula that can go in the budget sheet under C2/3. The formula has to find all the rows in the Transactions sheet that have a matching category to column A in the row with the formula on it. Then, it needs to return the sum of the D and E columns for the row that is matching. And do this for the entire Transactions sheet for each time the formula is entered into any cell on the Budget Sheet. – kingfoot Jan 17 '17 at 4:38
  • In your initial question I wasn't sure whether you want the formula for the Spent column or for the Remaining. But it is clear now. Also (this I'm still not sure) if the In and Out sums need to be added together, or you need the sum as the Out minus the In. – Yisroel Tech Jan 17 '17 at 6:58
0

This SUMIF (two of them in the formula, one for the In column and one for the Out) formula should do the trick:

=(SUMIF(Sheet2!$B$2:$B$1000,"CategoryA",Sheet2!$D$2:$E$1000))+(SUMIF(Sheet2!$B$2:$B$1000,"CategoryA",Sheet2!$E$2:$E$1000))

It'll look in column B (from row 2 to 1000, change to your situation) for CategoryA and if it is CategoryA it will add the number in columns D and E to the total sum.

Just change the two instances of CategoryA to CategoryB for row 3 on the Transection Sheet.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.