using INDIRECT to set structured table reference in formula and return COUNTIF result based on another column value

    Hi, hope someone can help.


    This works:

    =COUNTIFS(Table1[Role],3,Table1[Skill1],1)

    Returning a count of instances of "1" where the Role column value in Table1 is 3


    Can someone please help explain why this doesn't work if C4 contains Table1 and D4 contains Skill1

    =COUNTIFS(Table1[Role],3,(INDIRECT(C4&"["&D4&"]",1)


    My aim is to inject the table and column references from cell values in C4 and D4.


    The INDIRECT syntax works with count but I cant get it to work with the condition of the other column (role) value.


    Thanks.

    That formula can't be what you're using as it is invalid. You need:


    =COUNTIFS(Table1[Role],3,INDIRECT(C4&"["&D4&"]"),1)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Love 1

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login