Countif conditionally?

[spacely]

Hi,

I have 2 columns of text. There may or may not be empties in either column. And some rows may be hidden. So, I need to count up the number of times when there IS an entry in the 1st column (A) but NO entry in the next column over (B).

Thanks in advance...

Dave

 

[Aladin Akyurek]

One of...

=COUNTIFS(A:A,”?*”,B:B,””)

=COUNTIFS(A:A,”<>”,B:B,””)

 

[spacely]

Those seem to return 0. Even if I do shift+ctrl+enter...

 

[mr_wizard]

=COUNTIFS(A1:A17,"<>""",B1:B17,"")

This one worked for me.

 

[Aladin Akyurek]

Those seem to return 0. Even if I do shift+ctrl+enter...

These are not array formulas, so no control+shift+enter needed.

Care to post 5 rows from A and 5 rows from B?

 

[Aladin Akyurek]

=COUNTIFS(A1:A17,"<>""",B1:B17,"")

This one worked for me.

The suggestions should work if you use right double quotes!

 

[spacely]

=COUNTIFS(A1:A17,"<>""",B1:B17,"")

This one worked for me.

That one returns 29, instead of 6, probably because a bunch of rows are filtered?

just try:
dave1 bill1
bill2
dave3 bill3
dave4 bill4
dave5
dave6 bill6


And filter out some blank rows, ....

Does that work?

 

[spacely]

...it didn't paste for you right, but put a space or tab before that 1st bill2, and after that 5th dave5

 

[Aladin Akyurek]

That one returns 29, instead of 6, probably because a bunch of rows are filtered?

just try:
dave1 bill1
bill2
dave3 bill3
dave4 bill4
dave5
dave6 bill6


And filter out some blank rows, ....

Does that work?

...it didn't paste for you right, but put a space or tab before that 1st bill2, and after that 5th dave5

Row\Col	A​	B​
2​	dave1	bill1
3​		bill2
4​	dave3	bill3
5​	dave4	bill4
6​	dave5
7​	dave6	bill6

Counting the text occurrences in A while B is empty or blank as you asked for in your initial post, would yield 1 as result. Is this not what you had in mind?

 

[spacely]

Yes, but now use excel's filters to filter out row 4 with dave3. What's the answer? And then use the filters to filter out row 6 with dave5. What's the answer then? Answer should depend on the filter...so it works only on what's visible....