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

Microsoft Excel

Last Comment
[ fanpages ]
[ fanpages ]

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
John

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.

John

Thanks again.
[ fanpages ]

You're very welcome.

Good luck with the rest of your project.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY