Data Format Change -> Need Help with Arrays/Match/Index/SumIf combo (others too probably!)

[GreganDunn]

I have a very large spreadsheet where I data is entered/saved in a very awkward way. I need to reformat the data to something that I can work with.

I've created a very small subset of my data to use to work our way through my problem here, current form on the left, desired form on the right.

What I need to happen in the DESIRED DATA FORMAT, is to create a formula in the squares with the 1's and 2's that references the old data, and for each "Char"acter (Char001, Char002, ... 100 in total!) and "Grade" (GR, BR, ... 18 grades in total), search the old data format and COUNT the number of each of the "Items" (GreenArmor, GreenBioChem, ... 110 in total!) and place a number in the cell.

I've been poking around and trying things for the past few hours and not gotten anywhere... Any help you guys can provide would be awesome. Even just providing insights on the functions I need to get familiar with and study to sort this out.

[TABLE="width: 862"]
<tbody>[TR]
[TD="colspan: 3"]CURRENT DATA FORM[/TD]
[TD][/TD]
[TD="colspan: 5"]DESIRED DATA FORM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Char001[/TD]
[TD]Char002[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Char001[/TD]
[TD="colspan: 2"]Char002[/TD]
[/TR]
[TR]
[TD]GR[/TD]
[TD]GreenArmor[/TD]
[TD]GreenArmor[/TD]
[TD][/TD]
[TD][/TD]
[TD]GR[/TD]
[TD]BR[/TD]
[TD]GR[/TD]
[TD]BR[/TD]
[/TR]
[TR]
[TD]GR[/TD]
[TD]GreenCritical[/TD]
[TD]GreenBioChem[/TD]
[TD][/TD]
[TD]GreenArmor[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GR[/TD]
[TD]GreenDamage[/TD]
[TD]GreenCritical[/TD]
[TD][/TD]
[TD]GreenBioChem[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GR[/TD]
[TD]GreenEnergy[/TD]
[TD]GreenElementalArmor[/TD]
[TD][/TD]
[TD]GreenCritical[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GR[/TD]
[TD]GreenTankElemental[/TD]
[TD]GreenElementalArmor[/TD]
[TD][/TD]
[TD]GreenDamage[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GR[/TD]
[TD]GreenTankOffense[/TD]
[TD]GreenTankOffense[/TD]
[TD][/TD]
[TD]GreenElementalArmor[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BR[/TD]
[TD]BronzeCommon[/TD]
[TD]BronzeBioChem[/TD]
[TD][/TD]
[TD]GreenEnergy[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BR[/TD]
[TD]BronzeTankDefense[/TD]
[TD]BronzeHealth[/TD]
[TD][/TD]
[TD]GreenTankElemental[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BR[/TD]
[TD]GreenCritical[/TD]
[TD]BronzeHealth[/TD]
[TD][/TD]
[TD]GreenTankOffense[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BR[/TD]
[TD]GreenElementalArmor[/TD]
[TD]BronzeRare[/TD]
[TD][/TD]
[TD]BronzeBioChem[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]BR[/TD]
[TD]GreenElementalArmor[/TD]
[TD]BronzeTankElemental[/TD]
[TD][/TD]
[TD]BronzeCommon[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BR[/TD]
[TD]GreenEnergy[/TD]
[TD]GreenArmor[/TD]
[TD][/TD]
[TD]BronzeHealth[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BronzeRare[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BronzeTankDefense[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BronzeTankElemental[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[GreganDunn]

Looks like my first attempt at asking for help on this problem has failed at explaining the problem in a digestible manner... so I've setup my problem in a simpler way and attempted a solution that I felt should have worked but I get a #VALUE error. In the cell with the formula, I want all "Flower" to be counted when column header = 1 and the cell to the left is = A.

 

[GreganDunn]

I think this is my answer, just going to try it now: https://www.mrexcel.com/forum/excel-questions/738912-countif-index-match-row-column-criteria.html