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))}
Please help.
Thanks in advance,
Ricky
Bookmarks