Ask Your Question
0

Ask Your Question 0 Conditional Counting Using Multiple Columns where one condition is Not Blank and the other is Blank

asked 2016-03-15 23:00:47 +0100

cscj01 gravatar image

I have a Calc spreadsheet where two columns may or may not contain a value. If the first column contains a value, I want to count the second column if it is blank. I have been working with the COUNTIFS function, but I can't seem to get it to work. I tried =COUNTIFS(C2:C203,<>"",D2:D203,"") but I get an Err:510. I tried something like =COUNTIFS(C2:C203,COUNTA(C2:C203),D2:D203,"") but this gives 0 which is not correct. Finally, I tried =COUNTIFS(C2:C203,<>ISBLANK,D2:D203,ISBLANK) with a result of Err:510. The answer for this sheet should be 43. That is column C has 43 non blank entries where the corresponding row entry in column D is blank.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2016-03-15 23:44:44 +0100

m.a.riosv gravatar image

updated 2016-03-16 19:15:26 +0100

Not possible to put the comparators out of the quotes.

image description


Edited 20160316

With strings:
CountIfs_sample.ods

image description

edit flag offensive delete link more

Comments

If I use =COUNTIFS(C2:C203;"<>";D2:D203;""), I get 0 for an answer. My data is not numeric, it is alphanumeric. Does that make a difference?

cscj01 gravatar imagecscj01 ( 2016-03-16 16:43:59 +0100 )edit

Thanks, that took care of it. I'm going to have to think about the function name and how it relates to the issue.

cscj01 gravatar imagecscj01 ( 2016-03-17 04:44:29 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-15 23:00:47 +0100

Seen: 47 times

Last updated: Mar 16 '16