Count how many cells contain this string with lookup value-CodePudding

I have a large table of IDs and file quality from files that pertain to that ID. There are multiple files that constitute a data point (imaging data, volumetric in manner) so there are multiple rows per ID. A sample table similar in manner is posted below.

The goal is to create a function that will count how many "good" and "bad" values are per lookup subject in the SubjID column. There are ranges I want to follow, where if >50% of the G_R_B values for that SubjID are good, than G_R_B = G, 50% is good = R, and x<50% is good = B.

I will then sort and get unique value per subject ID.

Does anyone know how to do this? I tried using VLOOKUP and COUNTIF and found ways to do it

CodePudding user response：

If you can't use latest Excel formulas, you can use:

=IF(COUNTIFS($A$2:$A$7,"good",$B$2:$B$7,B2)/COUNTIF($B$2:$B$7,B2)>0.5,"G",IF(COUNTIFS($A$2:$A$7,"good",$B$2:$B$7,B2)/COUNTIF($B$2:$B$7,B2)<0.5,"B","R"))

Result:

COUNTIF used to find how many times value in B2 appears in B column.

COUNTIFS used to find how many times value in B2 appears in B column, with criteria "good" in column A

EDIT:

Updated formula according to your comment and edit:

=IF(COUNTIFS($A$2:$A$21,"good",$B$2:$B$21,B2)/COUNTIF($B$2:$B$21,B2)>0.8,"G",IF(COUNTIFS($A$2:$A$21,"good",$B$2:$B$21,B2)/COUNTIF($B$2:$B$21,B2)>=0.2,"R","B"))

Result: