Is there a way to count the number of occurrences of a category and assign a value in Excel?-CodePudding

Suppose there's a table with the following characteristics:

Subject    Topic     Issue  Subject RAG Rating
Biology    Cells     Y      
Biology    Mitosis   Y      
Maths      Algebra   N      
Computing  Python    N      
Computing  R         Y

Where Subject RAG Rating is a blank column. I want to populate this column with either R if the subject has two or more issues; A if the subject has 1 issue; or G if the subject has no issues.

Is there a formula that can do this and return a value for each row that looks like:

Subject    Topic     Issue  Subject RAG Rating
Biology    Cells     Y      R
Biology    Mitosis   Y      R
Maths      Algebra   N      G
Computing  Python    N      A
Computing  R         Y      A

I thought that perhaps COUNTIF might work, i.e. count the number of Y or N, but I'm unsure how you can tell Excel to populate the rows with R, A or G. Any help appreciated!

CodePudding user response：

Depending on your version of Excel, you can do this in several ways.

I strongly suggest that you turn your data into an Excel Table object with Ctrl-T or Insert > Table. That has benefits for writing formulas and addressing cells.

If you have an Office 365 subscription, you can use the LET() function to avoid duplicating parts of the formula:

=LET(CountResult,COUNTIFS([Subject],[@Subject],[Issue],"y"),IF(CountResult>1,"R",IF(CountResult=1,"A","G")))

If you don't have the LET() function, go the conventional way:

=IF(COUNTIFS([Subject],[@Subject],[Issue],"y")>1,"R",IF(COUNTIFS([Subject],[@Subject],[Issue],"y")=1,"A","G"))

CodePudding user response：

Another one:

=LOOKUP(COUNTIFS(A:A,A2,C:C,"Y"),{0,1,2},{"G","A","R"})