My apologies if I have repeated myself. As I thought I may have closed off
the question yesterday.

Hi
Following on from this and thank you for your help, is there a way that this
will select in col D all characters except 1 or 2 (eg I would like to
accept everthing except A)

Thanks

Anne

"T. Valko" wrote:

Try this...

If column N contains numbers only (or possibly empty cells)

=SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2:D10="R"),--(N2:N10<0))

Better to use cells to hold the criteria:

P1 = QN
Q1 = IM
R1 = R

=SUMPRODUCT(--(B2:B10=P1),--(C2:C10=Q1),--(D2:D10=R1),--(N2:N10<0))

--
Biff
Microsoft Excel MVP

Try the below
=SUMPRODUCT((B2:B10="QN")*(C2:C10="IM")*(D2:D10<" ")*
(D2:D10<"A")*(N2:N10<0))

'if blanks cells in D are to be counted then
=SUMPRODUCT((B2:B10="QN")*(C2:C10="IM")*(D2:D10<" A")*(N2:N10<0))

--
Jacob


"enna49" wrote:

My apologies if I have repeated myself. As I thought I may have closed off
the question yesterday.

Hi
Following on from this and thank you for your help, is there a way that this
will select in col D all characters except 1 or 2 (eg I would like to
accept everthing except A)

Thanks

Anne

"T. Valko" wrote:

Try this...

If column N contains numbers only (or possibly empty cells)

=SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2:D10="R"),--(N2:N10<0))

Better to use cells to hold the criteria:

P1 = QN
Q1 = IM
R1 = R

=SUMPRODUCT(--(B2:B10=P1),--(C2:C10=Q1),--(D2:D10=R1),--(N2:N10<0))

--
Biff
Microsoft Excel MVP

see your other post

--
Biff
Microsoft Excel MVP


"enna49" wrote in message
...
My apologies if I have repeated myself. As I thought I may have closed
off
the question yesterday.

Hi
Following on from this and thank you for your help, is there a way that
this
will select in col D all characters except 1 or 2 (eg I would like to
accept everthing except A)

Thanks

Anne

"T. Valko" wrote:

Try this...

If column N contains numbers only (or possibly empty cells)

=SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2:D10="R"),--(N2:N10<0))

Better to use cells to hold the criteria:

P1 = QN
Q1 = IM
R1 = R

=SUMPRODUCT(--(B2:B10=P1),--(C2:C10=Q1),--(D2:D10=R1),--(N2:N10<0))

--
Biff
Microsoft Excel MVP