I want formula for multiple criteria using ih differnt column
 

example

col-A COL-B COL-C COL-D COL-E
AA 1 C/S F 05.APR.08
AA 4 S/S S 02.MAY.08
BB 2 C/S S 03.MAY.08
CC 1 C/S F 10.APR.08
AA 0.5 C/S F
DD 2 A/S S
BB 10 C/S F

I WANT THE RESULT IF COL-A=AA, IF COL-E = NON BLANK OR DATE,IF COL-D=S,
COL-B <2, SUM(COL-B)

PLEASE HELP IN THIS
MAX NO OF ROWS MAY BE UPTO 40000 DATAS.

AWAITING YOUR HELP UREGENT PLEASE

S.P.SAMY

I want formula for multiple criteria using ih differnt column
 

=SUMPRODUCT(--(A2:A8="AA"),--((ISNUMBER(E2:E8))+(D2:D8="S")),--(B2:B8<2),B2:B8)

Sumproduct only works on defined ranges, not full columns, prior to Excel
2007.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PERANISH" wrote in message
...
example

col-A COL-B COL-C COL-D COL-E
AA 1 C/S F 05.APR.08
AA 4 S/S S 02.MAY.08
BB 2 C/S S 03.MAY.08
CC 1 C/S F 10.APR.08
AA 0.5 C/S F
DD 2 A/S S
BB 10 C/S F

I WANT THE RESULT IF COL-A=AA, IF COL-E = NON BLANK OR DATE,IF COL-D=S,
COL-B <2, SUM(COL-B)

PLEASE HELP IN THIS
MAX NO OF ROWS MAY BE UPTO 40000 DATAS.

AWAITING YOUR HELP UREGENT PLEASE

S.P.SAMY

I want formula for multiple criteria using ih differnt column
 

Thanks for yr immediate reply.Since i am in leave, i cant reply immediately.
Anyhow, i will apply this formula in my datas, will comeback soon.

Once again thanks

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A8="AA"),--((ISNUMBER(E2:E8))+(D2:D8="S")),--(B2:B8<2),B2:B8)

Sumproduct only works on defined ranges, not full columns, prior to Excel
2007.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PERANISH" wrote in message
...
example

col-A COL-B COL-C COL-D COL-E
AA 1 C/S F 05.APR.08
AA 4 S/S S 02.MAY.08
BB 2 C/S S 03.MAY.08
CC 1 C/S F 10.APR.08
AA 0.5 C/S F
DD 2 A/S S
BB 10 C/S F

I WANT THE RESULT IF COL-A=AA, IF COL-E = NON BLANK OR DATE,IF COL-D=S,
COL-B <2, SUM(COL-B)

PLEASE HELP IN THIS
MAX NO OF ROWS MAY BE UPTO 40000 DATAS.

AWAITING YOUR HELP UREGENT PLEASE

S.P.SAMY