Excel formula help

Occasional Contributor

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:



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



Please help. Thank you.

3 Replies
Best Response confirmed by Queenie Lai (Occasional Contributor)



Instead of


use it numeric equivalent


and you don't need


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


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?




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


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)