1 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Locking part of countifs formula

I have a table at work and I'm using a countifs formula to basically see how many consultants of a certain type in a given week report >0 hours. When I drag that formula across the next week's cell the formula changes the "employee type" column too, which I don't want it to do. If the range were like A1:A10, I could just lock it with $, but since it's in the format of tablename[columnname] that doesn't work.

What function do I use instead?

For instance, for the week ending 1/25 I want the formula to look like:

=countifs(Table[Employee Type],"specific employee type",Table[01/25/26],">0")

and when I drag it to the next column over, I want it to look like:

=countifs(Table[Employee Type],"specific employee type",Table[02/01/26],">0")

However, it instead shows:

=countifs(Table[Column adjacent to employee type],"specific employee type",Table[02/01/26],">0")

So I just want the date part of the function to change, not the first part. Any ideas? Let me know if more info is needed.

submitted by /u/cnaiurbreaksppl
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#AI formula generation techniques
#Excel alternatives for data analysis
#natural language processing for spreadsheets
#generative AI for data analysis
#rows.com
#Excel compatibility
#financial modeling with spreadsheets
#countifs
#formula
#table
#employee type
#consultants
#report
#hours
#lock
#drag
#range
#specific employee type
#date
#week