+ Reply to Thread
Results 1 to 6 of 6

Using Countif to count cells greater than zero in a macro

  1. #1
    Registered User
    Join Date
    10-24-2013
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    3

    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.

    Thank you all in advance!
    Attached Files Attached Files
    Last edited by FDibbins; 10-25-2013 at 04:59 PM. Reason: Code tags added

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,615

    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.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,247

    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.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I have added them for you this time, please try and remember to use them for future reference
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    10-24-2013
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    3

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

    Quote Originally Posted by AlphaFrog View Post
    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. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,615

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

    Try this..

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-24-2013
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    3

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count cells (not value) greater than 0
    By rwhite713 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-27-2013, 10:41 PM
  2. [SOLVED] Can we use countif to count how many are greater than (rather than equal to) a constant?
    By studiosa in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2013, 09:42 AM
  3. Replies: 6
    Last Post: 02-24-2012, 01:15 PM
  4. Replies: 3
    Last Post: 05-19-2010, 04:09 PM
  5. Count non-contiguous cells where value is greater than 0
    By anre5180 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-22-2006, 09:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1