Thank you for your post!
You might see pre-made solutions using Search | Made for FileMaker.
I'm also going to move this thread from the FileMaker Community Feedback Space (which is specifically for input on the Community itself) to the Discussions Space where you should receive more views and potentially more feedback on this topic!
I would ideally need a count(if) type function.
This is a pretty common thing to see in this forum. No such function exists, but there are ways to get that same result. Which approach works best, depends on the design of your tables/relationships and how you design the layout on which you want to report those counts.
ExecuteSQL( ). This function can use count to count your records with a WHERE clause to do the "if" part that limits the count to a specific set of records.
A Relationship can be set up that matches only to the records you want to count (such as all active employees). Then the Count ( ) function works as you are using the relationship to get the "If" part of this to work.
A Filtered portal can filter for a specific category of records and a "count of" summary field or the found set layout object can be inserted into the portal's single portal row to show a count of just those related records that pass the filter.
A summary report can be set up where you sort the records into categories with a sub summary part to serve as a "sub header" for each such group of records. Put a "count of" summary field into that sub summary layout part and you get a count of the records in each group. Remove the body layout part from the layout and you get one row of data for each category with a count for each.
"Cross tab" reports are also possible--where the subtotals get arranged into columns instead of rows. Though these are more complex to set up and there are a number of very different methods that can be used to produce such a report. You can research "Cross tab" here in the forum to find discussions of the different methods you might use for such a report.
there isnt a native count(if) calculation but you can accomplish the same results with many different ways. The "classic" way with relationships employers to departments, the same with filtering. you have to tag everyone with the department unique value