lundi 5 décembre 2016

Excel SUM IF with multiple array criteria

I am trying to do a "sum if" that check the month and year are the same as the criteria on the income sheet column A as well as a income code in column D are all valid criteria. I have used the option with the * as the AND function.

I am using array formulas that work independently "shown below', but I a, not when I try to combine them I cannot get it to provide the desired result.

Income Summary table

Income table

THIS CODE WORKS:

"Check Month and Year" =SUM(IF((MONTH(Income!$A$3:$A$10000)=MONTH($A3))*(YEAR(Income!$A$3:$A$10000)=YEAR($A3)),Income!$C$3:$C$10000))

"Check Code" =SUMIF(Income!$D$3:$D$10000,"="&$C$2,Income!$C$3:$C$10000)

THIS DOES NOT WORK:

=SUM(IF((Income!$D$3:$D$10000*(MONTH(Income!$A$3:$A$10000)=MONTH($A3))*(YEAR(Income!$A$3:$A$10000)=YEAR($A3),"="&$C$2)),Income!$C$3:$C$10000))

Hope you can help

Thanks

Aucun commentaire:

Enregistrer un commentaire