mercredi 27 mai 2015

Multiple Condition IF Statement in ARRAY forumula EXCEL

I am trying to create an array formula in Excel with multiple conditions, however it does not work (results in 0). If I remove the AND and evaluate one condition at a time, the formula works, but I need excel to evaluate all three conditions and sum the values only if all three are met. Any hints are appreciated. Thanks!

WORKS:

=SUM(IF($I$2:$I$9999<>"",$U$2:$U$9999))

=SUM(IF(YEAR($I$2:$I$9999)=YEAR(AD2),$U$2:$U$9999))

=SUM($M$2:$M$9999="",$U$2:$U$9999))

DOES NOT WORK:

=SUM(IF(AND($I$2:$I$9999<>"",YEAR($I$2:$I$9999)=YEAR(AD2),$M$2:$M$9999=""),$U$2:$U$9999))

Ctrl+Shift+Enter is used to enter the formula.

Aucun commentaire:

Enregistrer un commentaire