Cauld
Registered User regular

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:

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.

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.

0

## Posts

L Ron HowardonOrogogusonWell, 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"?

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.

Cauldon