Forum Discussion

Queenie Lai's avatar
Queenie Lai
Copper Contributor
Jan 02, 2019
Solved

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: =...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 02, 2019

    Hi,

     

    Instead of

    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))),"")
Queenie Lai's avatar
Queenie Lai
Copper Contributor
Jan 03, 2019

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))),"")

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 03, 2019

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)

Resources