• 414K Members
• 8,498 Online
• 477K Conversations
SOLVED

New Contributor

# Excel formula help

I am working on the following formula for a report. Could anyone please help figure out why the following formula does not work when I added 'AND' to the formula?

The BOLD part is not working:

=IFERROR(INDEX(Mastersheet2018!\$C:\$C,SMALL(IF(AND(Mastersheet2018!\$L:\$L=\$B\$3,Mastersheet2018!\$A:\$A=\$M\$3),ROW(Mastersheet2018!\$C:\$C)-MIN(ROW(Mastersheet2018!\$C:\$C))+1),ROWS(\$C\$5:C5))),"")

However, without 'AND', this formula works perfectly.

=IFERROR(INDEX(Mastersheet2018!\$C:\$C,SMALL(IF(Mastersheet2018!\$L:\$L=\$B\$3,ROW(Mastersheet2018!\$C:\$C)-MIN(ROW(Mastersheet2018!\$C:\$C))+1),ROWS(\$C\$5:C10))),"")

3 Replies
Solution

# Re: Excel formula help

Hi,

`AND(Mastersheet2018!\$L:\$L=\$B\$3,Mastersheet2018!\$A:\$A=\$M\$3)`

use it numeric equivalent

`(Mastersheet2018!\$L:\$L=\$B\$3)*(Mastersheet2018!\$A:\$A=\$M\$3)`

and you don't need

`-MIN(ROW(Mastersheet2018!\$C:\$C))+1`

since first part is always equal to 1, result is always zero.

Plus in first formula you use ROWS(\$C\$5:C5), in second one ROWS(\$C\$5:C10), perhaps they are copy pasted from different rows.

And in general you may use non-array variant of the formula

`=IFERROR(INDEX(Mastersheet2018!\$C:\$C,AGGREGATE(15,6,1/(Mastersheet2018!\$L:\$L=\$B\$3)/(Mastersheet2018!\$A:\$A=\$M\$3)*ROW(Mastersheet2018!\$C:\$C),ROWS(\$C\$5:C5))),"")`

# Re: Excel formula help

The formula works perfectly for these 2 criteria: Mastersheet2018!\$L:\$L=\$B\$3 and Mastersheet2018!\$A:\$A=\$M\$3

If I want to add a third criteria (Mastersheet2018!\$AB:\$AB<>0) can I still use the following formula?

=IFERROR(INDEX(Mastersheet2018!\$C:\$C,AGGREGATE(15,6,1/(Mastersheet2018!\$L:\$L=\$B\$3)/(Mastersheet2018!\$A:\$A=\$M\$3)*ROW(Mastersheet2018!\$C:\$C),ROWS(\$C\$5:C5))),"")

# Re: Excel formula help

Yes, you may use as many criteria as you need, like

`1/criteria1/criteria2/criteria3`

If any one of them is FALSE above returns an error (division on zero) and AGGREGATE with second parameter 6 ignores all errors making selection only from elements where all criteria are TRUE (division on 1)

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies