New To Mac-Forums?

Welcome to our community! Join the discussion today by registering your FREE account. If you have any problems with the registration process, please contact us!

Get your questions answered by community gurus Advice and insight from world-class Apple enthusiasts Exclusive access to members-only contests, giveaways and deals

Join today!

 
Start a Discussion
 

Mac-Forums Brief

Subscribe to Mac-Forums Brief to receive special offers from Mac-Forums partners and sponsors

Join the conversation RSS
OS X - Apps and Games Discussion of applications and games available for Mac OS X.

EXCEL Formula Help


Post Reply New Thread Subscribe

 
Thread Tools
fearlessfreap24

 
fearlessfreap24's Avatar
 
Member Since: Feb 09, 2005
Location: Near San Diego, CA
Posts: 2,339
fearlessfreap24 is just really nicefearlessfreap24 is just really nicefearlessfreap24 is just really nicefearlessfreap24 is just really nice
Mac Specs: MacBook Pro 13" | MacBook Pro 13" | Mac Mini 2GHz C2D

fearlessfreap24 is offline
here is what i want to do. I am trying to make a ledger spreadsheet. I want it all on one sheet. there are thirteen categories and 3 bank accounts. i have each category broken into separate sheets at the moment but the wife refuses to use more than 1 sheet. in the picture provided is an example.

let's say that i want to know how much money fun money i have in checking.

the formula i want needs to be able to look at the account column and the category column. sum the amount column where the two columns equal checking and fun.

somewhere on the sheet off to the right i want to be able to put a matrix depicting the 13 categories vertically and the 3 accounts horizontally. this will allow me to look quickly and see how much money in each category is in each account. this is why i need the formula.

i thought of building a truth table, but i felt it was going to be to big.

i was also thinking what if i input onto this main sheet and it was automatically input into the separate category sheet. then i could make the matrix on the main sheet reflect numbers compiled from the category sheet. i do not know how to do this first part.

i am using MS Office for Mac 2004 Ver 11.0

any help or advice is appreciated.
Attached Images
File Type: png Picture 1.png (12.4 KB, 6 views)

QUOTE Thanks
Sawday

 
Sawday's Avatar
 
Member Since: Nov 19, 2006
Location: York, UK
Posts: 1,028
Sawday is a jewel in the roughSawday is a jewel in the roughSawday is a jewel in the rough
Mac Specs: 24" Aluminium, 2.8 GHz, 4Gb RAM. iPad2, iPhone4

Sawday is offline
Try this link Excel: Count If with Multiple Criteria. Excel Count with Multiple Criteria.

I do something very similar and though it took a bit of time to get it exactly right it now works for me though the logic in the formula seems counter intuitive. Read the whole page but I think the section that will solve your problem is 'SUM as an Array Formula'. The example there is a bit more complicated than you need but if you look what is happening you can simply it. One important point - having typed in the formula don't just press Enter but CTRL SHIFT ENTER.

Hope this helps.

Happiness is not getting what you want, but wanting what you get.
QUOTE Thanks
fearlessfreap24

 
fearlessfreap24's Avatar
 
Member Since: Feb 09, 2005
Location: Near San Diego, CA
Posts: 2,339
fearlessfreap24 is just really nicefearlessfreap24 is just really nicefearlessfreap24 is just really nicefearlessfreap24 is just really nice
Mac Specs: MacBook Pro 13" | MacBook Pro 13" | Mac Mini 2GHz C2D

fearlessfreap24 is offline
thanks!! there is some useful information there.

QUOTE Thanks
fearlessfreap24

 
fearlessfreap24's Avatar
 
Member Since: Feb 09, 2005
Location: Near San Diego, CA
Posts: 2,339
fearlessfreap24 is just really nicefearlessfreap24 is just really nicefearlessfreap24 is just really nicefearlessfreap24 is just really nice
Mac Specs: MacBook Pro 13" | MacBook Pro 13" | Mac Mini 2GHz C2D

fearlessfreap24 is offline
after doing some research on the website that Sawday provided, i found that a Pivot Table worked best. the only problem is that you have to manually have to update the Pivot Table. What i mean by this is when i add information to the table that the Pivot Table is based off of the Pivot Table does not reflect the entry. i have to click inside the Pivot Table and hit a button to update the Pivot Table.I will try to find out how to update it automatically.

the first example is by category with the accounts subtotaled and a grand total by category.

the second example is by account with the categories subtotaled and an account grand total. if you drag the account header to the other side of category, it will rearrange the Pivot Table.
Attached Images
File Type: png Picture 1.png (28.6 KB, 4 views)
File Type: png Picture 2.png (25.4 KB, 3 views)

QUOTE Thanks
Sawday

 
Sawday's Avatar
 
Member Since: Nov 19, 2006
Location: York, UK
Posts: 1,028
Sawday is a jewel in the roughSawday is a jewel in the roughSawday is a jewel in the rough
Mac Specs: 24" Aluminium, 2.8 GHz, 4Gb RAM. iPad2, iPhone4

Sawday is offline
The Sum as an Array formula definately updates 'in real time' - Have been amending a database here for the last hour and my analysis sections are right up to date with no manual intervention. Suggest you google pivot table and see if this is a 'feature'.

Happiness is not getting what you want, but wanting what you get.
QUOTE Thanks
fearlessfreap24

 
fearlessfreap24's Avatar
 
Member Since: Feb 09, 2005
Location: Near San Diego, CA
Posts: 2,339
fearlessfreap24 is just really nicefearlessfreap24 is just really nicefearlessfreap24 is just really nicefearlessfreap24 is just really nice
Mac Specs: MacBook Pro 13" | MacBook Pro 13" | Mac Mini 2GHz C2D

fearlessfreap24 is offline
i had done some research and it is not feasible for Macs.

QUOTE Thanks

Post Reply New Thread Subscribe


« EXCEL (Office 2008) running slowly, freezing? | Applescript Editor question »
Thread Tools

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off
Forum Jump

Similar Threads
Thread
Thread Starter
Forum
Replies
Last Post
how to copy a row of excel to firefox chidambaram OS X - Apps and Games 0 06-04-2009 03:26 AM
Updating Excel document over a network? Garnon OS X - Apps and Games 3 05-26-2009 03:41 AM
Microsoft Excel Find.Seranish OS X - Apps and Games 1 12-01-2008 03:42 PM
Formula Bar for Excel rreiser Running Windows (or anything else) on your Mac 0 07-31-2008 05:38 PM
Changing serial for Excel for Mac pegasus OS X - Apps and Games 1 04-08-2008 09:27 AM

All times are GMT -4. The time now is 06:50 PM.

Powered by vBulletin
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
X

Welcome to Mac-Forums.com

Create your username to jump into the discussion!

New members like you have made this community the ultimate source for your Mac since 2003!


(4 digit year)

Already a member?