Excel countif help

Discussion in 'Mac Programming' started by jrock2004, Aug 22, 2011.

  1. jrock2004 macrumors 6502

    Joined:
    May 4, 2008
    Location:
    PA
    #1
    A B
    1 High low
    2 low
    3

    So I want to get a count the empty spots of column B. I can do this. But I if column A next to the cell is empty I do not want that to count. Any ideas?
     
  2. mkrishnan Moderator emeritus

    mkrishnan

    Joined:
    Jan 9, 2004
    Location:
    Grand Rapids, MI, USA
    #2
    The easiest way I know is to do something of the form:

    =SUM(IF(B1:B6<>"",IF(C1:C6="",1,0),0))

    In this case, if the item in B is not blank, then one point is summed for each blank item in C; if C is not blank, then it's 0, if B is blank, it's 0. I didn't understand all the double negatives, so you would modify it to whatever combination of cells being blank or not blank you want.

    It's an array formula (i.e. you hit ctrl-shift-enter after you type it in to make it work).
     
  3. jrock2004 thread starter macrumors 6502

    Joined:
    May 4, 2008
    Location:
    PA
    #3
    I should only get a point if column C does not have anything it but column be that is next to it is not empty.

    So with my example above I should get the answer of one. Is that what your thing will do?
     
  4. mkrishnan Moderator emeritus

    mkrishnan

    Joined:
    Jan 9, 2004
    Location:
    Grand Rapids, MI, USA
    #4
    Yes.
     
  5. jrock2004 thread starter macrumors 6502

    Joined:
    May 4, 2008
    Location:
    PA
    #5
    Hmm, well I am not getting that, I am getting a higher number
     
  6. Raid macrumors 68020

    Raid

    Joined:
    Feb 18, 2003
    Location:
    Toronto
    #6
    Arrays are by far the most under utilized and most powerful methods of aggregating data out there. Learn all you can about them!

    As for your example try this
    =SUM(IF((B1:B3="")*(A1:A3<>""),1,0))
    Array's handle "and" statements and "or" statements differently. Here I used two arguments in the logical test using brackets around each test and using an asterisk as the "and" statement. If you wanted to use an "or" statement use the plus sign '+' instead of the asterisk.

    The logic for multiple arguments for arrays are mathematical; if 1 is true and 0 false the logic is applied like this:
    1*0 = 0
    1*1 = 1
    1+0 = 0
    1+1 = 1 {<- anything >=1 is true}
     
  7. jrock2004 thread starter macrumors 6502

    Joined:
    May 4, 2008
    Location:
    PA
    #7
    That worked. Not sure if I am happy about having to do Ctrl+shift+enter to update values but I understand. Thanks
     
  8. mkrishnan Moderator emeritus

    mkrishnan

    Joined:
    Jan 9, 2004
    Location:
    Grand Rapids, MI, USA
    #8
    You don't have to ctrl-shift-enter to update values. That happens automatically (using either method). You just have to ctrl-shift-enter when you modify the formula in the cell containing the array.....
     

Share This Page