Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

CountIfs and Blanks, not blanks

Barry Walls
edited 12/09/19 in Archived 2016 Posts

Hi Guys

 

I have two columns of data that I want to summarise with a count into a specific cell where the follpowing conditions are true:

 

The first column is NOT blank AND the second column IS Blank.

 

Not sure if CountIf's is right for this. Any help would be appreciated!

 

All the best

 

Barry

Comments

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭

    Hi Barry,

     

    I think the following will do what you're looking for assuming that the cells of interest are in columns A and B from rows 1 to 10.

     

    The COUNTIFS() function can only check for  non-blank specific values as arguments. To get around this, create a column C next to B with the following formula:

     

    =IF(AND(NOT(ISBLANK(A1)), B1 = ""), "Y", "")

     

    This checks for column A being not blank and B being blank and needs to be entered in cells C1 - C10.

     

    Then, using the COUNTIFS() function do the following"

     

    =COUNTIFS(C1:C10, "Y") to get the number of times the condions were met.

     

  • Thank you for your reply Jim. I'll try that tomorrow and let you know how I get on, one way or another.

     

    All the best

     

    Barry

     

     

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭

    Just a thought, if you changed the first IF() function to put a 1 instead of a "Y" in the cell, you could use a simple =sum(C1:C10) as the last step to get the number of times the conditions were met.

  • Barry Walls
    edited 09/29/16

    Jim, 

     

    Thankyou so much! I got the "1"s to display in the colums I needed but for some weird reason, when I use the SUM finction to count the range, the value returned is always zero. The column is properly formatted for text/numbers and the formular is correct 

     

    =SUM([Count1]1:[Count1]360) 

     

    So not sure if you can shed any light on this?

     

    Thanks again for your help. 

     

    All the best

     

    Barry

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭
    edited 09/29/16

    Barry,

     

    If you see the 1s but they are not counting, maybe they are text 1s. In the formula that puts 1s in a cell if the conditions are met make sure they don't have quotes around the 1s.

  • I got it Jim. I just used count rather than sum and it worked perfectly after I used your "1" suggestion. 

     

    I'm now trying to work out how to reference the last cell in a column that has a value and grab the value for part of another formula. I have Xn rows which will change so I've numbered them and want to get the value in the the bottom cell (which counts the number of records). Any ideas?

     

    All the best

     

    Barry

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭
    edited 09/29/16

    Barry, nothing comes to mind on getting the value in the last cell that has a value.

This discussion has been closed.