Excel Formula Problem

[Haris1234]

Hi, I have an excel sheet with thousands of rows four individual persons and hundreds of columns of categories. Each four consecutive columns can be clubbed as category. I want to do an analysis of this excel sheet and come up with an output as mentioned below in the sample.(Excel attached)

[#2]

@Haris1234 

Not sure I would do this with thousands of rows & hundreds of columns (I would rather look at restructuring the sheet to enable this analysis and potentially other ones) but see attached file

[#3 · Accepted solution]

@Haris1234 

For best analysis change your structure from crosstabular form into a flat table:

Person - Category - Sub - Type - Value

Then it is just a matter of a quick pivot table.

[#4]

@Haris1234 Since you mention "thousands of rows and hundreds of columns" I'd suggest you take a look at Power Query to flatten your data, as suggested by @Detlef Lewin . 

From there, you can create the desired output within Power Query, in Power Pivot (Data Model) or in a regular pivot table.

All of these are demonstrated in the attached file. 

[#5]

@Haris1234 

Another Power Query + Power Pivot option (attached) in case you would need to analyse on others than A

[#6]

Thank You All.
Flattening file and pivoting worked for me :)

[#7]

formula enter in excel sheet

[#8]

@alishyangoijam285 

Species name	Frequency %	Density	Relative frequency (RF)	Relative density(RD)	IVI=RF+RD	ni/N
Paspalum sp	85	5.4	13.2192846	#DIV/0!
Cynodon dactylon	65	10.9	10.1088647	#DIV/0!
Dracocephalum moldavica	78	3.9	12.13063764	#DIV/0!
Centella asiatica	40	3.15	6.220839813	#DIV/0!
Alternanthera sp	50	2.45	#DIV/0!	#DIV/0!
cyperus sp	50	2.85	#DIV/0!	#DIV/0!
Eclipta sp	5	0.45	#DIV/0!	#DIV/0!
Spilanthes sp	35	1.05	#DIV/0!	#DIV/0!