Search code examples
excelexcel-formulavlookupcountif

Count how many cells contain this string with lookup value

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.

enter image description here

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.

enter image description here

Does anyone know how to do this? I tried using VLOOKUP and COUNTIF and found ways to do it when all duplicate IDs are stripped, but I am struggling to take into account when duplicate IDs occur.

EDIT: SAMPLE TABLE TO SEE WHAT TO DO IF RANGES ARE INVOLVED (see comment)

enter image description here

Solution

  • 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:

    enter image description here

    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:

    enter image description here