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
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?
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.