Curly Brackets MULTIPLE criteria

[rplim2016]

Hi Mr. Excel users,

I have a simple problem that I can't solve.

{=SMALL(IF(AND(R:R=1,Q:Q=O6),P:P),20)}

Above formula is not working

Columns:
R - Cell value = 1 is only considered to be included
Q - Cell value = "April" will only considered to be included

But it isn't working, I don't know if the formula AND is working in Curly brackets. Please help.

 

[pgc01]

Hi

Try:

=SMALL(IF(R:R=1,IF(Q:Q=O6,P:P)),20)

confirmed with CSE

 

[rplim2016]

Hi

Try:

=SMALL(IF(R:R=1,IF(Q:Q=O6,P:P)),20)

confirmed with CSE

Thanks a lot! Very helpful. I knew there was wrong about using AND and OR, but I didn't think to use another IF. Thank you!

 

[pgc01]

You're welcome.

Remarks:
1 - it's usually more efficient if in the formulas you don't use whole columns. You can use instead some row range, like rows 1:1000
2 - an alternative syntax for this formula would be:

=SMALL(IF((R:R=1)*(Q:Q=O6),P:P),20)

or, for rows 1:10000

=SMALL(IF((R1:R10000=1)*(Q1:Q10000=O6),P1:P10000),20)

confirmed with CSE

 

[rplim2016]

Hi there,

How about OR?

Below formula runs in AND..
=SMALL(IF((R1:R10000=1)*(Q1:Q10000=O6),P1:P10000),20)

How can I make this to OR function?

Thanks

 

[pgc01]

Hi there,

How about OR?

Below formula runs in AND..
=SMALL(IF((R1:R10000=1)*(Q1:Q10000=O6),P1:P10000),20)

How can I make this to OR function?

Thanks

Hi

Replace the "*" with "+".

 

[rplim2016]

Hi

Replace the "*" with "+".

Amazing, thanks!

 

[pgc01]

You're welcome.