+ Reply to Thread
Results 1 to 7 of 7

Only calculate results when data is present in corresponding cell

  1. #1
    Registered User
    Join Date
    07-29-2013
    Location
    Burlington, Iowa USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Only calculate results when data is present in corresponding cell

    I have two sets of data in two columns I am running reports and averages on, a pretest and posttest.

    A few of these sets do not have complete data.

    What I want to do is exclude the results if the data is not complete for that test. Example data below has two data sets that are not complete and need to be removed from the results.

    If I were to run this count I only want to be able to show completed tests for pretest:

    =COUNTIF(K:K,"<=1.9")

    The desired result would be 7. How could I build the formula to exclude results with missing data? I hope I am explaining this right. Thanks

    PRETEST POSTTEST
    1.2 2.7
    1.4 2.9
    1.5 3
    1.7 0.9
    1.9 1.6
    1.9
    1.9 1.9
    1.9 4.5
    2.2
    2.3 3.5
    2.3 3.9

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Only calculate results when data is present in corresponding cell

    Hi

    Using the sample data provided, there are only 3 entries that <=1.9, not 7 (0.9,1.6 and 1.9)?

    You can get this with the formula you suggested, or with this...
    =COUNTIFS(K2:K12,"<>""",K2:K12,"<=1.9")
    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

  3. #3
    Registered User
    Join Date
    07-29-2013
    Location
    Burlington, Iowa USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Only calculate results when data is present in corresponding cell

    I apologize, the table did not post right.

    In the below table there are 5 tests. Only 3 complete. I want the formula to exclude the tests that are not complete. In my example, I wanted the CountIF to return a number of complete pretests in column K that were at or below 1. In this case the scenario would return 2 for completed tests and the pretests with a 1.

    K | L
    1 | 3
    1 |
    1 | 2
    2 | 4
    | 3

  4. #4
    Registered User
    Join Date
    07-29-2013
    Location
    Burlington, Iowa USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Only calculate results when data is present in corresponding cell

    Still searching for a workaround on this. I am finding a thread on a "TRIM" function?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Only calculate results when data is present in corresponding cell

    have you tried the countifS() function? it allows more than 1 criteria

  6. #6
    Registered User
    Join Date
    07-29-2013
    Location
    Burlington, Iowa USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Only calculate results when data is present in corresponding cell

    FDibbins, I am currently using that for another table to count the number of occurrences in a column.

    =COUNTIFS(K:K,">=5",K:K,"<=6")

    How would I want to modify this? In this case if Column L also has data for tests, but not all rows have data in both Columns K & L.

    I only want to return a count for column K if L has data in that row.

    Thanks much

  7. #7
    Registered User
    Join Date
    07-29-2013
    Location
    Burlington, Iowa USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Only calculate results when data is present in corresponding cell

    Figured it out using SUMPRODUCT

+ 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. [SOLVED] Auto Hiding rows based on range/data present or not present.
    By raze in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 02-10-2013, 11:27 AM
  2. [SOLVED] Use data Grid to calculate results.
    By Cheeseburger in forum Excel General
    Replies: 12
    Last Post: 05-02-2012, 05:11 PM
  3. Formula assistance - find duplicate and present results
    By craighaylett in forum Excel General
    Replies: 3
    Last Post: 10-01-2010, 06:34 AM
  4. Copy For Cell Values Present and not Formulas Present
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2009, 12:10 PM
  5. count occurence and present results
    By Hobbes2006 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-07-2006, 04:45 PM

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