Forum Discussion
Sum Products for multiple employees and funds
I am trying to figure out a way to return results that look at 2 columns and cells. Right now it captures if my first column (Column A) is different but I also need it to read column B. Currently my formula is returning the same amount in Cell E regardless of what is in Column B so my 24 rows are showing the same amount. My current formula reads: =SUMPRODUCT($H$64:$H$241,1*($A4=$A$64:$A$241),1*($F$64:$F$241<>""))-SUMPRODUCT($G$64:$G$241,1*($A4=$A$64:$A$241),1*($F$64:$F$241<>"")). I know I need to fix this somehow to also read $B4=$b$
4 Replies
Perhaps
=SUMPRODUCT($H$64:$H$241,($A4=$A$64:$A$241)*($B4=$B$64:$B$241)*($F$64:$F$241<>""))-SUMPRODUCT($G$64:$G$241,($A4=$A$64:$A$241)*($B4=$B$64:$B$241)*($F$64:$F$241<>""))
HansVogelaar That appears to have worked. Now I need to also fix my balance column to do the same thing. Currently I have:
=SUMIF($A$64:$A$241,$A4,$H$64:$H$241)-SUMIF($A$64:$A$241,$A4,$G$64:$G$241)
You can use SUMIFS for that. Note that the order of the arguments is different:
=SUMIFS(H$64:$H$241,$A$64:$A$241,$A4,$B$64:$B$241,$B4)-SUMIFS($G$64:$G$241,$A$64:$A$241,$A4,$B$64:$B$241,$B4)
