November 30, 2021
I'm having trouble with anchoring columns when using the name in the formula.
My summary table has headers like "Jan", "Feb", etc in Row 86.
In Row 87, which I hide, I have dates like "1/1/22", 2/1/22".
In Column B is the Owner name, like "Bob", "John".
So I'm trying to sum up all of the tasks in "TaskTable" for "Bob" during each month where the "category" is not blank.
Below works great for January. If I drag it across for Feb, Mar, etc. the entries like "TaskTable[Category]" change. How would you anchor those with the $ to keep them from changing? Or do I have to avoid using the names in a formula and always use the alphanumeric references with $ anchors?
=COUNTIFS(TaskTable[Start],">="&$C$87,TaskTable[Start],"<="&EOMONTH($C$87,0), TaskTable[Owner],$B88, TaskTable[Category], "<>")
July 16, 2010
See this tutorial on absolute referencing Table structured references, which explains the double square bracket technique like so:
=COUNTIFS( TaskTable[[Start]:[Start]],">="&$C$87, TaskTable[[Start]:[Start]],"<="&EOMONTH($C$87,0), TaskTable[[Owner]:[Owner]],$B88, TaskTable[[Category]:[Category]], "<>")