Hi everyone,
Hope everyone is well and healthy.
I'm having difficulty in writing a dynamic formula to count the number of competencies based on three criteria.
The formula will be written in column H6 onwards in the 'competency tracker' sheet while the list of competencies per subject are written in different sheets on the same workbook.
The table for the competencies already have table names and are written on the same columns (i.e., column A for the grade level, column B for the quarter and column C for the competencies)
The formula that I used, following the criteria Subject is English, Grade level is I and Quarter is 3rd, was:
=COUNTIFS(INDIRECT($E6&"!C:C"),1,INDIRECT($E6 & "!A:A"),'Competency Tracker'!$F6,INDIRECT($E6 & "!B:B"),'Competency Tracker'!G6)
The answer should be 6.
Any help will be very much appreciated. Thank you very much.View attachment 52460
View attachment 52462
Hope everyone is well and healthy.
I'm having difficulty in writing a dynamic formula to count the number of competencies based on three criteria.
The formula will be written in column H6 onwards in the 'competency tracker' sheet while the list of competencies per subject are written in different sheets on the same workbook.
The table for the competencies already have table names and are written on the same columns (i.e., column A for the grade level, column B for the quarter and column C for the competencies)
The formula that I used, following the criteria Subject is English, Grade level is I and Quarter is 3rd, was:
=COUNTIFS(INDIRECT($E6&"!C:C"),1,INDIRECT($E6 & "!A:A"),'Competency Tracker'!$F6,INDIRECT($E6 & "!B:B"),'Competency Tracker'!G6)
The answer should be 6.
Any help will be very much appreciated. Thank you very much.View attachment 52460
View attachment 52462