asked on

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

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John

ASKER

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

John

ASKER

Thanks again.

[ fanpages ]

You're very welcome.

Good luck with the rest of your project.