Multiple drop menus in formulas

Question

Level 1

8 points

Multiple drop menus in formulas

Hi there, i am using my mac os x 10.6.8 with Numbers 09 and am very new to creating formulas. I am using a template and am making my own alterations. As you can see in the pic attached i have one spreadsheet (transaction) that all information is entered and from there i want that information to be directed to the corresponding "account" gaph. Eg each graph is a seperate account, from the transaction spreadsheet i have drop down menus set up for each account and for each category. Am I able to have both account and category drop menu in the formula for the relative corresponding graph? Is there a way i can achieve this? Thank you for your assitance. L

MacBook Pro, Mac OS X (10.6.8), Automator

Posted on Nov 14, 2013 5:42 PM

Reply

Answer 1

Best reply

Wayne Contello

Level 7

26,535 points

Nov 15, 2013 7:03 AM in response to Lindo77

Lindo,

you want to sum amounts when they meet two conditions:

1) The account matches the table you are summing in

2) the category matches

to do this you use the SUMIFS() function which allows more than one condition to sum a particular row.

I would suggest adding a new header row to each summary table and in cell A1 store the account type (AMX, Master Card, Goal Saver, etc).

so the current formula:

B2=sumif($Category, A2, Transactions :: Amount)

should change to (move down a row because we added one more header row):

B3=sumifs(Transactions :: Amount, Transactions :: Account, $A$1, Transactions :: Category, A3)

Duplicate this table and change the account in cell A1

Reply

Answer 2

Lindo77 Author

Level 1

8 points

Nov 15, 2013 10:04 PM in response to Wayne Contello

Wayne,

thanks for gettign back to me.

That was a great help. Only issue was that when i typed in the =sumifs(Transactions :: Amount, Transactions :: Account, $A$1, Transactions :: Category, A3), the transactions account came back saying it is an invalid reference. But as i was deleting the Account in the formula i accedently stumbled on leaving the A in there and that worked a treat.

Any explanation why the Account didnt work.

Other than that it works beautifully.

Many thanks,

L

Reply