Organizing Data - SUMIFS/INDEX-MATCH

[phillygirl777]

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.

 

[Somendra Misra]

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.

 

[phillygirl777]

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

 

[Somendra Misra]

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.

 

[phillygirl777]

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!

 

[Somendra Misra]

Thanks, for your feedback.

Regards.

 

[GraH - Guido]

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.

 

[phillygirl777]

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.

 

[phillygirl777]

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!

 

[bosco_yip]

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

 

[GraH - Guido]

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.