Extracting Data based on multiple criteria

[May1]

Hi,

Below is my data source (here there are 4 rows and 4 columns but actually the data has 225k rows and 77 columns) :

Deal	Comment	FX	F X	F/X	FOREX
24754704​	FX	Hit	NoHit	NoHit	NoHit
24754784​	F X	NoHit	Hit	NoHit	NoHit
24753342​	F/X	NoHit	NoHit	Hit	NoHit
24753458​	USD	NoHit	NoHit	NoHit	NoHit

I want results (preferably in a pivot table) that shows me this:

	Hit	NoHit
FX	1​	3​
F X	1​	3​
F/X	1​	3​
FOREX	0​	4​

Please note - I cannot transpose the rows to columns as number of rows is greater than 225k.

Please help..

 

[p45cal]

Power Query and Pivot in attached

 

[May1]

Power Query and Pivot in attached
View attachment 84551

Amazing, thank you p45cal!

 

[bosco_yip]

Formula way,

In K2, formula copied across right and down:

=COUNTIF(OFFSET(INDEX($C$2:$F$2,MATCH($J2,$C$1:$F$1,0)),,,230000),K$1)