I am trying to list “members” with various Grades from 1 to 8, but, I then want to know who is ready for advancement, say from 2nd to 3rd Grade.

I have attached the file and hope it gets to you in one piece.

If, when opened you go to the 1st Mons Tab and on to col Y to BL.

Col Y to AV records where the candidate undertook his 1st to 6th Grade with the date of the ceremony worked.
Col AW to BD are the result of an =if(blank(cell no.),””,1) function which will leave a corresponding “1” or blank in the respective cell, depending if the argument is true or false.
The function =Countif(array) will then add up the number of 1s in the column.

Col BE to BL are the inverse of the cols AW to BD in that they are supposed to display by showing a 1 or a blank. Such that if a candidate has achieved a 2nd Grade then the column BG would show a 1 in the 3rd Grade column and columns BH to BL would show blanks in that particular row.

So, columns AW to BD show the grades achieved.
Columns BE to BL show the very next grade to be undertaken.

The problem is that using the function “=if(isblank(* *),””,1) on columns Be to BL doesn’t return the expected result.

You can simply delete the 1s in cols Be to BL as they are only illustrations of the results I should expect.

Attached Files

club Losses and Gains Sept 1st 2022.xlsx (462.4 KB, 7 views)