Excel Sheet Specific Formula not working

The formulas in there are not necessarily wrong, it depends on what exactly you are intending to count. Example: D4 counts all cells in column CS which contain the number 2. There is no way for me to decide whether that is the correct way to determine how many rows are about "wiring and switch". You know the business logic which determines what exactly needs to be counted.
There are some simple mistakes however. For example, many of the formula's in D do not point to the same rows as other formulas in column D. This is bad practice which you can easily avoid by converting your data on "Worksheet" to a table. See https://jkp-ads.com/Articles/Excel2007Tables.asp