• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 849
  • Last Modified:

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
John Hurst
John Hurst
  • 2
  • 2
1 Solution
[ fanpages ]IT Services ConsultantCommented:

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

Please see the attached workbook for an example.


John HurstBusiness 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
John HurstBusiness Consultant (Owner)Author Commented:
Thanks again.
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Good luck with the rest of your project.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now