Avatar of John
JohnFlag for Canada 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
Microsoft Excel

Avatar of undefined
Last Comment
[ fanpages ]

8/22/2022 - Mon
[ fanpages ]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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

Thanks again.
[ fanpages ]

You're very welcome.

Good luck with the rest of your project.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.