1

I am trying to count the number of rows which do not have the values "X", "Y" or "Z". Also, the values should not be blank.

I am using the formula below to count the number of rows that have the column A row value with 'IPhone" and column C value with "Z" but I couldn't get it to work with the "NOT" function.

=COUNTIFS('iPhone Users'!A:A,"iPhone",'iPhone Users'!C:C,"Z")
1

Try along these lines

=COUNTIFS(A:A,"<>iphone",A:A,"<>",C:C,"<>z")

The chevron brackets are the "NOT". In the screenshot the formula returns a count of all items in column A that are NOT blank, do NOT have "iphone" as the value and where column C is NOT "z". The result is 2, which relates to rows 2 and 10, Android with no Z in column C.

enter image description here

1

You could try

1) Using Sum with Array formula. (Enter formula and then Ctrl+Shift+Enter)

=SUM(IF((Sheet2!A:A<>"")*(Sheet2!A:A<>"X")*(Sheet2!A:A<>"Y")*(Sheet2!A:A<>"Z"),1,0))

2) Using Countifs

=COUNTIFS(A1:A5,"<>X",A1:A5,"<>Y",A1:A5,"<>Z",A1:A5,"<>"&"")

In order to check for blanks, you would want to check for "". However, to input it in the formula, you cannot input it directly with ", but instead need to use "<>"&"" which means concatenate <> and "" into a single expression

  • Using array formulas on whole columns is not a very good idea, unless you like slow spreadsheets. – teylyn Jun 3 '15 at 5:37
  • Good point. I probably would also define a table for my data, and reference the table column instead (eg Table1[Column1]), or at least a named dynamic range to prevent calculations on the whole column which in general is a bad idea. – EDM Jun 3 '15 at 7:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.