Summarise a matrix

Hello,

I would like to know if someone could help me with the following;

I have a database in a matrix, where rows and columns have a country name, then the character "_", then a sector code. I would like to create a summary table, as seen on the screenshot below, which would sum every corresponding row/column. I tried with SUMIFS and DSUM but I have troubles adapting to my case.

Many thanks in advance,

EDIT: had to resubmit as I have cases where the RIGHT function doesn't work, as sectors don't always have three characters.

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

And you are using Office 2016?

In B13 copied across and down:

=SUMPRODUCT((RIGHT($A$2:$A$10,3)=$A13)*(RIGHT($B$1:$G$1,3)=B$12)*($B$2:$G$10))

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Try this:

=SUMPRODUCT((MID($A$2:$A$10,FIND("_",$A$2:$A$10)+1,99)=$A13)*((MID($B$1:$G$1,FIND("_",$B$1:$G$1)+1,99)=B$12)*($B$2:$G$10)))

How about

Formula:

Please Login or Register  to view this content.

EDIT:
If you could have Values like CA01 this will fail

Try:

=SUMPRODUCT((TEXTAFTER($A$2:$A$10,"_",1)=$A13)*(TEXTAFTER($B$1:$G$1,"_",1)=B$12)*$B$2:$G$10)