troubleshooting Question

Reverse type of lookup in a SUMIF situation

Avatar of John
JohnFlag for Canada asked on
Microsoft Excel
4 Comments1 Solution942 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros