Count the values by ignoring cells which contain NA

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    I have tried the below mentioned formula to calculate the % of updates from past 2 months. Updated the month value (in numbers) in column 'J'. For a few of the entries no updates required and have marked them as NA. While calculating the percentage, I am excluding NA values and count the number of updated entries in past 2 months. I am using the below mentioned formula :


    =(COUNTIF(J4:J345,">(INT(MONTH(TODAY())-2))"))/(COUNTIF(J4:J345,"<>"&"NA"))


    If I replace (INT(MONTH(TODAY())-2)) with the number 3, the result is correct. However, if I try to make it a generic one with the formula, it does not provide the correct value and instead, gives me the count of "NA" in that column.

    Re: Count the values by ignoring cells which contain NA


    Hi ravs_1006, and Welcome.


    It would be helpful if you could upload a sample Excel workbook of what you describe. Live data often clarifies at a glance.


    How do I attach a file to a post?

    To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

    On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either


    from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

    To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have


    completed one of the boxes, click 'Upload'.

    Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

    Re: Count the values by ignoring cells which contain NA


    Without seeing the file my only guess would b do you need the quotation marks around the (INT(MONTH(TODAY())-2)) in your formula in other words would

    Code
    [/COLOR][COLOR=#333333]=(COUNTIF(J4:J345,>(INT(MONTH(TODAY())-2))))/(COUNTIF(J4:J345,"<>"&"NA"))[/COLOR][COLOR=#333333]

    work? Purely a guess.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login