# Sumproduct, Sumif, or Array Formula?

1. ## Sumproduct, Sumif, or Array Formula?

Hello Experts,
I really need your help with this one. How do you modify this formula to add this first part?

If each cell within range(\$B\$2:\$B\$13)<>"", match the year and month of cell D\$1, then sum the data range(\$C\$2:\$C\$13),

if each cell within range(\$B\$2:\$B\$13) is blank, then choose range(\$A\$2:\$A\$13) to match to the year and month of cell D\$1, then sum the data range(\$C\$2:\$C\$13). [The following formula somewhat does this part except that it doesn't have the ability to toggle between column A or B in each row]

=SUMPRODUCT((YEAR(\$A\$2:\$A\$13)=YEAR(D\$1))*(MONTH(\$A\$2:\$A\$13)=MONTH(D\$1))*(\$C\$2:\$C\$13))

I've tried this array formula but it doesn't work.
{=SUMPRODUCT(IF(YEAR(\$B\$2:\$B\$13)<>"",(YEAR(\$B\$2:\$B\$13)=YEAR(D\$1)),(YEAR(\$A\$2:\$A\$13)=YEAR(D\$1)))*IF(MONTH(\$B\$2:\$B13)<>"",(MONTH(\$B\$2:\$B\$13)=MONTH(D\$1)),(MONTH(\$A\$2:\$A\$13)=MONTH(D\$1)))*(\$C\$2:\$C\$13))}

Ricky  Register To Reply

2. Figured out a solution. Out of curiousity, is there a more condensed formula version than this one?

=SUMPRODUCT((YEAR(\$A\$2:\$A\$13)=YEAR(D\$1))*(MONTH(\$A2:\$A\$13)=MONTH(D\$1))*(\$B\$2:\$B\$13="")*(\$C\$2:\$C\$13))
+SUMPRODUCT((YEAR(\$B\$2:\$B\$13)=YEAR(D\$1))*(MONTH(\$B\$2:\$B\$13)=MONTH(D\$1))*(\$C\$2:\$C\$13))

Thanks,
Ricky  Register To Reply