Ask Your Question
0

Calc: Number of cells which don't have any of a set of values

asked 2018-10-02 15:53:29 +0100

alex72gr gravatar image

updated 2018-10-02 19:25:00 +0100

My problem:

I have a spreadsheet with quite much information. I need to check a large number of rows (actually I need to check each one of them separately). Some cells are empty (no value), some others have the character (value) "X", and some other have string values. So, I what I need to check for every single row (or more precisely, for a specific range of every single row) is how many cells: 1) are not empty AND 2) do not the have the value (character) "X" AND 3) do not have the any of the (string) values of three specific cells of my spreadsheet.For example, if I need to check the row 7 (range F7:Y7) and I need to know how many cells are not empty AND do not have the value (character) "X" AND do not have any of the values (strings) of the cells A7, B7 and C7, which formula should I use? I think that I should use the COUNTIF function, but I'm not sure what I should do...

Thanks in advance!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2018-10-02 18:59:15 +0100

erAck gravatar image
=SUMPRODUCT(F7:Y7<>"";F7:Y7<>"X";F7:Y7<>A7;F7:Y7<>B7;F7:Y7<>C7)

See also online help.

edit flag offensive delete link more

Comments

Thanks! It works!

alex72gr gravatar imagealex72gr ( 2018-10-02 19:17:26 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-10-02 15:53:29 +0100

Seen: 37 times

Last updated: Oct 02 '18