Negative return formula

[LozSale]

I am working with a series of sheets within a file and need to consolidate some information.

Columns are:

Column N is a series of numbers from blank through 0 - 9.

Column B contains criteria in text format.

The folumala that I need should find, for instance:

All instances of "8" in column N that do not contain "AAC", "REME", "RLC" in column B.

Can anyone help.

Many thanks

Loz

 

[nbrcrunch]

just to be quite clear, the following will count the qualifying rows. "Finding" records is a bit general and when you mentioned "consolidate" with "finding" I am not sure if I am providing what you want.

=sumproduct(--(N1:N100=8),--(b1:B100<>"AAC"),--(b1:B100<>"REME"),--(b1:B100<>"RLC"))

header rows cannot be included. If you have header row(s), change the first cell reference to the first data row.

change the last data row from 100 to whatever your last data row is.

 

[Andrew Poulsom]

Do you want to count the entries? Example:

=SUMPRODUCT(--(Sheet2!N1:N20=8),((Sheet2!B1:B20<>"AAC")*(Sheet2!B1:B20<>"REME")*(Sheet2!B1:B20<>"RLC")))

 

[barry houdini]

You could use something like

=SUMPRODUCT(--(N1:N10=8),--ISNA(MATCH(B1:B10,{"AAC","REME","RLC"},0)))

 

[LozSale]

Thanks Barry Houdini,

Yours was the winner. I may not have made myself clear but I needed all those entries which did not meet the criteria e.g. all those entries that return 6 in N but do not meet the text criteia in B.

Thanks very much

Loz

 

[nbrcrunch]

that is funny. all the formulas did the same exact thing except stated differently. If one worked, they will all work.

 

[Aladin Akyurek]

that is funny. all the formulas did the same exact thing except stated differently. If one worked, they will all work.

While that's right, the formula the OP picked out would be fastest (and probably more intelligible).