Multiple IF

**samy2** · 06-13-2007, 10:38 AM

Hi,

can it be possible,

If column A “date” is greater than August 31, 2004 and
Column B(Date1) is greater than column A + 3 years, OR If column A “date” is less than or equal to August 31, 2004 and Column B is greater than August 31, 2007 Then
-Sum the SME available in Column C .

pls refer attachment below,

Thanks,
Samy2

**sweep** · 06-13-2007, 11:28 AM

Hi, in cell H2,

=IF(OR(AND(A2>38229,B2>W2+(3*365)),AND(A2<=38229,AA2>39325)),C2,"")

**samy2** · 06-14-2007, 06:15 AM

Hi Sweep,

yes, its working fine with single row, but when i take a range of like A2:A6, B2:B6, C2:C6, it is not working. so could u pls tell me how it will work for a range.

thanks,
samy2

**arthurbr** · 06-14-2007, 08:40 AM

Extend the formula by double-clicking the handle at the lower right corner of the cell containing your formula after selecting it

**samy2** · 06-19-2007, 05:10 AM

Hi Sweep,

I'm unable to execute for a range, so can you tell me how to go for a range like A2:A6, B2:B6, C2:C6. Its working fine with single row.

thanks
Samy2

**samy2** · 07-18-2007, 10:25 AM

Hi all,

Can anybody tell me how to go for a range, I'm unable to execute for a range, like A2:A6, B2:B6, C2:C6. Its working fine with single row.

Formula for single row is,
=IF(OR(AND(A2>38229,B2>A2+(3*365)),AND(A2<=38229,B2>39325)),C2,"")

Please refer attachment,
thanks,
Samy2

**samy2** · 08-21-2007, 06:07 AM

Hi all,

Can anybody tell me how to go for a range, I'm unable to execute for a range, like in below formula i assumed row 2A2, B2, C2) and itsIts working fine with a single row.
But when i try for multiple row, its not working?
Formula for single row is,

=IF(OR(AND(A2>38229,B2>A2+(3*365)),AND(A2<=38229,B 2>39325)),C2,"")

Please refer attachment,
thanks,
Samy2

**arthurbr** · 08-21-2007, 06:41 AM

Hi Samy2
it would be better to open a new thread so that everyone can use the solutions.

Cheers

**Mark@Work** · 08-21-2007, 11:45 AM

Hi Samy,

What exactly are you asking?
Is all you want to do copy this solution into other rows?
Position your pointer over the bottom right corner of the cell which currently holds the formula.
Your pointer should change shape to a +;
press and hold the left hand mouse button;
drag that point in the direction you want to replicate the formula;
release the mouse button when the shaded area covers the cells you want to fill.

The row numbers and column letters will adjust acording to the current position, unless you preceed them with a $.

Mark.

**samy2** · 08-21-2007, 01:34 PM

Hi Mark,

Actually, I'm not getting the correct result when I try to sum multiple rows values like what i'm getting the result for a single row formula as given below,But when i try for multiple row(like for A2:A6, B2:B6,C2:C6), its not working, it is giving zero value
Formula for single row is,
=IF(OR(AND(A2>38229,B2>A2+(3*365)),AND(A2<=38229,B 2>39325)),C2,"")

For multiple row,
=IF(OR(AND(A2:A6>38229,B2:B6>A2:A6+(3*365)),AND(A2:A6<=38229,B2:B6>39325)),C2,"")
Actually i need a formulae for multiple rows?

Please refer attachment,
thanks,
Samy2

**Mark@Work** · 08-22-2007, 01:51 PM

Sorry I took some time getting back.

I think what you are after are array formulas.
Just press the Ctrl, Shift and Enter buttons simultaneously rather than just pressing the Enter button when entering your formula.

It is a slightly odd method that EXCEL uses to tell the formulas to work on ranges rather than single values. Once you get used to it it is very powerful and gives you access to a host of new possibilities.

Mark.