arraysexcelif-statement

Multiple Condition IF Statement in ARRAY formula 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.

Solution

  • You can't use AND function in these sort of formulas because it returns a single result, not an array - try using * to simulate AND, i.e.

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

    confirm with CTRL+SHIFT+ENTER

    ....or an "non-array" version with SUMPRODUCT

    =SUMPRODUCT(($I$2:$I$9999<>"")*(YEAR($I$2:$I$9999)=YEAR(AD2))*($M$2:$M$9999=""),$U$2:$U$9999)

    .....or possibly SUMIFS

    =SUMIFS($U:$U,$I:$I,">="&DATE(YEAR(AD2),1,1),$I:$I,"<"&DATE(YEAR(AD2)+1,1,1),$M:$M,"")

    Edit: you probably don't need to check that column I is non-blank if you are also checking the YEAR for that column - it can't match the year anyway unless it's possible AD2 might be blank?