I trying to find a solution to count the number of times a specific number is entered in the even rows of a column. The actual range of columns is row 18 to 205, e.g. C18:C205. The even cells in the column are entered with a number in the range of 1 to 10 but never zero. There are empty cells, but these are always at the end of the range, e.g. the numbers are entered sequentially commencing at C18. Either a date in the format of DD/MM/YYYY is entered into the corresponding cell in column B, e.g. B18 when entering the number into cell C18 or an alternative reference, e.g. LCE or a name or similar.
There are also numbers in the odd row of each pair of cells, e.g. C19 has a number that is the range of zero to 6. These should never be included in the calculation.
In the example attached the objective is to establish the number of occasions the number 4 is entered. The solution should only count the number of times “4” or another number from 1 to 10 is recorded in the even row of the column if there is a date in the corresponding cell in column B.
I have previously used the formula COUNTIF, e.g. =COUNTIF(C18,”4”)+COUNTIF(C20,”4”) etc. but this does not accommodate even rows where the number entered should be ignored where there is not date related in column B. I have incorporated a date into other solutions using SUMIFS, but this does not seem possible in this case?
Thanks in advance for any solution that might be available.
There are also numbers in the odd row of each pair of cells, e.g. C19 has a number that is the range of zero to 6. These should never be included in the calculation.
In the example attached the objective is to establish the number of occasions the number 4 is entered. The solution should only count the number of times “4” or another number from 1 to 10 is recorded in the even row of the column if there is a date in the corresponding cell in column B.
I have previously used the formula COUNTIF, e.g. =COUNTIF(C18,”4”)+COUNTIF(C20,”4”) etc. but this does not accommodate even rows where the number entered should be ignored where there is not date related in column B. I have incorporated a date into other solutions using SUMIFS, but this does not seem possible in this case?
Thanks in advance for any solution that might be available.