I want formula for multiple criteria using ih differnt column

P

PERANISH

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
 
B

Bob Phillips

=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)
 
P

PERANISH

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top