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.

CURRENT DATA FORM		DESIRED DATA FORM
	Char001	Char002			Char001	Char002
GR	GreenArmor	GreenArmor			GR	BR	GR	BR
GR	GreenCritical	GreenBioChem		GreenArmor	1		1	1
GR	GreenDamage	GreenCritical		GreenBioChem			1
GR	GreenEnergy	GreenElementalArmor		GreenCritical	1	1	1
GR	GreenTankElemental	GreenElementalArmor		GreenDamage	1
GR	GreenTankOffense	GreenTankOffense		GreenElementalArmor		2	2
BR	BronzeCommon	BronzeBioChem		GreenEnergy	1	1
BR	BronzeTankDefense	BronzeHealth		GreenTankElemental	1
BR	GreenCritical	BronzeHealth		GreenTankOffense	1		1
BR	GreenElementalArmor	BronzeRare		BronzeBioChem				1
BR	GreenElementalArmor	BronzeTankElemental		BronzeCommon		1
BR	GreenEnergy	GreenArmor		BronzeHealth				2
				BronzeRare				1
				BronzeTankDefense		1
				BronzeTankElemental				1

<tbody>
</tbody>

 

[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