# Using Countif to count cells greater than zero in a macro

1. ## Using Countif to count cells greater than zero in a macro

Ok... I've placed a test file that has the data in it for my question.

Right now I'm using the following macro to have the program sum column E within each department and place the total of the department in column H in the blank row after the last person in the department.

``Please Login or Register  to view this content.``
This takes care of my totals for the following:

Total (Column H) which totals RegHours (Column E)
Regular Total (Column I) which totals Regular Hours (Column F)
Overtime Total (Column J) which totals Overtime Hours (Column G)

Now what I need is to have a countif function that tells me using Regular Hours (Column F) if the employee's hours are greater than zero in each department, so I can know how many people actually worked this week in that department, and this would be in Employee Count (Column K) next to all the other totals.

The other thing I need, which would be very similar to the above need, is another countif function within a macro that looks at Overtime Hours (Column G) and does another countif greater than zero to tell me the number of employees that had overtime hours and that total would be in Employee's w/Overtime (Column L).

My problem arises from the fact that each week we delete people and add people to these departments and it cannot be a simple =countif(F2:F25,">0") and =countif(G2:G25,">0").

I'm sure I can manipulate the macro coding posted above to do this but I have played with it and cannot figure it out.

Any and all help would be greatly appreciated.

2. ## Re: Using Countif to count cells greater than zero in a macro

You could reference the entire column in the COUNTIF function.
=countif(F:F,">0")

1.) Empty cells are not greater than zero.
2.) Worksheet functions like countif will only evaluate within the used range when given an entire column to evaluate.

3. ## Re: Using Countif to count cells greater than zero in a macro

Hi and welcome to the forum

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

I have added them for you this time, please try and remember to use them for future reference

4. ## Re: Using Countif to count cells greater than zero in a macro

Originally Posted by AlphaFrog
You could reference the entire column in the COUNTIF function.
=countif(F:F,">0")

1.) Empty cells are not greater than zero.
2.) Worksheet functions like countif will only evaluate within the used range when given an entire column to evaluate.
While this is helpful, it still references the whole column, I need it to just do counts within each department. Knowing that the row numbers will change because of additions and subtractions within each department is the difficult part in my limited knowledge.

Thanks for helping out though!

5. ## Re: Using Countif to count cells greater than zero in a macro

Try this..

``Please Login or Register  to view this content.``

6. ## Re: Using Countif to count cells greater than zero in a macro

You guys on these forums are great...It's works perfectly. I'm going to look through and make sure I understand it. Really appreciate it!

There are currently 1 users browsing this thread. (0 members and 1 guests)