Excel Help

Cauld

I've seen a lot of really smart Excel people on here, so I hope someone can help.

I have Excel 2003 at work. We're slowly upgrading to 2007, but I still need a solution that will work on 2003.

I have a long list of data consisting of 4 columns. Acct number. Sub Acct number. Description. Amount. Each Acct number repeats anywhere from 0 to 100 times. Sub Acct numbers also exists multiple times. So do Descriptions. However each combination of Acct number and Sub Acct number is unique.

I want an equation in one cell to find an amount for a specific combination of Acct and Sub Acct number without creating any new columns.

So for example my data looks like this:

Acct       Sub Acct    Description  Amount
1          111        Fruits           7 
1          113        Vegetables      90 
1          224        Meat             3 
2          111        Apples           5 
2          113        Bananas        410 
3          111        Boats          555 
3          113        Boats          678 
3          224        Boats          800 
3          793        Boats          222

Questions:
1. I want to be able to find any amount given an Acct and a Sub Acct.
2. Is there some way to find the sum of the amounts for multiple Accts?

Notes:
I don't want to solve this using databases since there will be thousands of specific accounts
I can't use any functions like "SumIfs", because that was added in Excel 2007. Sumif is still good though.

L Ron Howard

Why can you not use SUMIFs? That's perfectly valid to use in Excel 2003. I'm using it in several spreadsheets that were made in 2003 and they work just fine.

Orogogus

A PivotTable would be ideal here, but I guess if you don't want to make any new columns this would be even worse.

Cauld

L Ron Howard wrote: »

Why can you not use SUMIFs? That's perfectly valid to use in Excel 2003. I'm using it in several spreadsheets that were made in 2003 and they work just fine.

Well, I guess I can try it in 2003 and see if it works. Are you still using 2003 now, or did you just import the spreadsheets into a newer version. I thought I tried making an equation with sumifs and it told me I'd used an invalid formula name or something. Are you sure you're not confusing "SumIf"s with "SumIfs"?

Orogogus wrote: »

A PivotTable would be ideal here, but I guess if you don't want to make any new columns this would be even worse.

I did actually create a Pivot Table to summarize all of the accounts, Maybe if I added in the sub accounts that could work, I'll check that out too.

The main thing is that I may not be around to support this creation when its being used and new accounts and sub accounts will almost certainly be added, so I don't want to make it too difficult.

The person who was setting this up before me had created thousands of lines of databases and she kept having to fix them and create more as she went, so I was trying to get a method that was easier overall. Or at least have equations that can be copy/pasted or modified slightly as things progressed. She's pretty good with Excel, but doesn't have much experience with Pivot Table.

The more I think about this the better it's sounding. I'm thinking either a more detailed Pivot Table or if the Sumifs thing works then it might be easier if I just switch it all to that.

Edit: I realized sometime last night that adding the sub accounts to the Pivot Chart won't help much, since the Pivot Chart will help me no more than just adding a column that combined the Acct and Sub account into a longer string would have helped. Maybe I can get away with some 'helper' cells on the same sheet as the Pivot Chart.