Log In
Register
Home
Active Member
Excel Expert
November 30, 2021
Offline
I'm having trouble with anchoring columns when using the name in the formula.
For example:
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], "<>")
See Answer
July 16, 2010
Offline
Hi Chris,
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]], "<>")
Mynda
Answers Post
1 Guest(s)
