Conditional formatting: not matching

[akkerstad]

If I would like cells with values that are not listed, with exception of empty cells, to turn red, how would I go about? For instance when I would like all cells with values that are not listed as criteria to turn red (not including empty cells)?

Thanks in advance!

EXAMPLE FORMATTING.xlsx

http://i.snag.gy/oxPXh.jpg

[etaf]

you can use
countif(E1:E7,A1)=0
and
A1<>""

combine as an AND
AND( A1<>"", COUNTIF($E$1:$E$7, A1)=0)

[akkerstad]

That's truly awesome. Why the A1 part though? I've tried to change cell A1 out for other cells in the function, which didn't produce the same result.

Why is only this 1 cell listed in the function?

[etaf]

only because i started in row 1
you can change the row number
BUT you also have to change the applies to range

so if your range is the same row
AND( A2<>"", COUNTIF($E$1:$E$7, A2)=0)
But the range it applies to needs to change to A2:??

conditional formatting will look in the cell that you told it to use as a range

so if its A1 : B10

then the formula would also have to have A1

otherwise it will be looking in say A10 - if the range was A10:B20
but use the formula and test cell A1 - and change Cell A10 if true

so to do A10:B20
the formula would need to be
AND( A10 < >"", COUNTIF($E$1:$E$7, A10)=0)

[akkerstad]

You're a king, thanks a million!

[etaf]

your welcome thanks for the rep

[akkerstad]

One more question: how could I create a function, which counts the total number of values that are NOT listed and marked as red in the table (range A2;B14)?

Thanks!

EXAMPLE FORMATTING.xlsx

[etaf]

One more question: how could I create a function, which counts the total number of values that are NOT listed and marked as red in the table (range A2;B14)?

I thought thats what we have
Are do you just want the actual number ?

I have added a column which counts the criteria and also wil count blanks in the range

[akkerstad]

Yes, I am looking for the actual number. It is the same idea as the function you created for the conditional formattting. However, I can't use that same function to give me the total number of values.

Basically, I would like Excel to add up all values that are marked as red, so that I know the total amount of values which are not listed as criteria (Wordpress, Microsoft, etc.).

[etaf]

cool - then thats what I have done
to count the actual red cells - would need VBA - if the solution I provided does not work

I also think we should be able to create a Array formula to count from the list - but i cant work that out at the moment

is the attached OK

[akkerstad]

You've counted the values that are listed as criteria (all Google related values), would it also be possible to count the values that are not listed as criteria (Wordpress, Microsoft, etc.)?

[etaf]

Dah!!!!

Well you could add all the cells in a range using
=ROWS(A1:B14) * COLUMNS(A1:B14)
and then take that away -

pretty messy

[akkerstad]

Cool, thanks again for all your help, much appreciated!

[etaf]

someone else may come up with a better solution