macOS Excel countif help

[jrock2004]

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?

 

[mkrishnan]

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).

 

[jrock2004]

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).

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?

 

[mkrishnan]

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?

Yes.

 

[jrock2004]

Hmm, well I am not getting that, I am getting a higher number

 

[Raid]

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?

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}

 

[jrock2004]

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}

That worked. Not sure if I am happy about having to do Ctrl+shift+enter to update values but I understand. Thanks

 

[mkrishnan]

That worked. Not sure if I am happy about having to do Ctrl+shift+enter to update values but I understand. Thanks

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.....