Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum column based on multiple criteria | Excel Worksheet Functions | |||
How do I sum column D and F based on multiple column criteria? | Excel Worksheet Functions | |||
How to add differnt font colors to Formula results | Excel Discussion (Misc queries) | |||
Multiple Criteria in same Column | Excel Worksheet Functions | |||
multiple criteria in one column | Excel Worksheet Functions |