Reverse type of lookup in a SUMIF situation

I have a fairly complex client workbook that makes use of SUMIF.  I have the following type of setup:

Account   Subtotaler  Data
=======   =========  ====
1001          100       $1,500
1002          100       $1,600
1003          102       $1,700
1004          101       $1,800
1005          100       $1,900
1006          102       $2,000
1007          101       $2,100
1008          101       $2,200
1009          100       $2,300
1010          103       $2,400

I can use SUMIF to the data for 100,  for 101, for 102, and for 103. This is standard SUMIF and works just fine.

I would like to make a report (worksheet) that displays the following:

100           1001      $1,500
                1002     $1,600
                1005     $1,900
                1009      $2,000

101           1004     $1,800
                1007    $2,100
                1008    $2,200

102           1003    $1,700
                1006    $2,000

103           1010    $2,400

This report is for a client, and it is meant to inform the client how subtotals (the 100 numbers) were calculated (the 1000 accounts).

Do you have some ideas of how I might do this?

Thank you, ... Thinkpads_User
LVL 105
JohnBusiness Consultant (Owner)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:

Perhaps a "simple" PivotTable is what you are looking for.

Please see the attached workbook for an example.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnBusiness Consultant (Owner)Author Commented:
I downloaded and looked at your example. That looks like it could work. I have numerous data columns (no issue) and only the two account/subtotaler columns.

I think I could make the report as just a pivot table (so as to look neat) because the data is in another worksheet.

I think as a first stab, this is what I am going to try. It will take me a while (learn pivot tables, implement, explain to client) but it looks like a good approach.

Thank you very much. I do appreciate the assistance.

... Thinkpads_User
JohnBusiness Consultant (Owner)Author Commented:
Thanks again.
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Good luck with the rest of your project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.