• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Organizing Data - SUMIFS/INDEX-MATCH

Hello,

Need some help organizing data (please see attached). The data tab has detailed data by product by region. The list is basically a classification database that translates each product into a category. I need to populate the summary tab to calculate how much profit each region generated in each category (the highlighted summary tab). Your help is greatly appreciated.
 

Attachments

  • Example 12.13.19.xlsx
    11.4 KB · Views: 9
Hi,

Here my take... use below formula on summary sheet B2 cell and copy across and down.

=SUM(IF(IF(B$1=List!$B$2:$B$11,List!$A$2:$A$11)=TRANSPOSE(Data!$B$1:$K$1),TRANSPOSE(Data!$B2:$K2)))

As this is an array formula use Ctrl+Shift+Enter to enter the formula.

Regards.
 
Thank you! I am not clear about <Ctrl>+<Shift>+<Enter>... I copied across, and B2 is the only cell with a result. Could you please clarify? I haven't dealt with arrays in a while. Thanks again
 
Go to B2 cell. Press F2..Ctrl +V to paste the formula. Than Hold Ctrl key, Shift Key together and press enter. Once you get result drag it to other cells.

Regards.
 
Sorry... it worked this time...
thank you !
Go to B2 cell. Press F2..Ctrl +V to paste the formula. Than Hold Ctrl key, Shift Key together and press enter. Once you get result drag it to other cells.

Regards.
Thanks, it did work. I am using Office 365. I was just used to { } format. Thanks again for all your help!
 
phillygirl777, since you are using Office 365, I can advise to use Power Query (Get & Transform) over Array formula, because it allows you to build the solution with simple mouse clicks.
 

Attachments

  • Copy of Example 12.13.19.xlsx
    23.3 KB · Views: 3
phillygirl777, since you are using Office 365, I can advise to use Power Query (Get & Transform) over Array formula, because it allows you to build the solution with simple mouse clicks.
Thanks! Unfortunately, I don't have this option at work. I also need to have the ability to shuffle the order of regions so it is not contingent on how it is sorted on the data tab, so I can't assume that the order will remain the same.
 
Thanks, for your feedback.

Regards.
In my original post I forgot to mention that the order in which locations appear is not static. My example includes 4 sites. The real-life situation includes over 300 rows that should be sorted, filtered, and manipulated the way the users see fit. Thanks!
 
64535

Since, the "Product" range in the "Data" and "List" sheet both in the same order,

so, the formula can be shortened to >>

In "Summary" sheet B2, formula (non-array formula) copied across and down :

=MMULT(0+Data!$B2:$K2,0+(B$1=List!$B$2:$B$11))

Regards
Bosco
 

Attachments

  • Example 12.13.19 (Mmult).xlsx
    12.4 KB · Views: 2
Last edited:
Thanks! Unfortunately, I don't have this option at work. I also need to have the ability to shuffle the order of regions so it is not contingent on how it is sorted on the data tab, so I can't assume that the order will remain the same.
It's an integrated function in Office 365 so why would it not be available for you?
Shuffling the order of regions is like super easy with the result table. Select the row and move it to the correct position. Or better use a reference table in which you define the sort order. Or use any of the Table sort options.

In my original post I forgot to mention that the order in which locations appear is not static. My example includes 4 sites. The real-life situation includes over 300 rows that should be sorted, filtered, and manipulated the way the users see fit. Thanks!
The PQ solution will work no matter how many rows there are, how the data is sorted or filtered. For me it is a strange given, users manipulate "raw data" to see a result they are after. I prefer to give "analytical power" (via charts/pivots/slicers) on the "summary", small example attached.

EDIT: uploading file was not working. I'll retry later.
 
Back
Top