Is there an excel formula to calculate all "like" entries from two criteria across two cells?
Solution 1:
It could be done by formula like this:
=SUM(($E$11:$E$27=$B4)*($F$11:$F$27=C$3)*$D$11:$D$27)
Note: This works for Excel 365 and newer. In older Excel versions this formula must be inserted as array formula (with CTLR+SHIFT+ENTER
)
See image:
I strongly suggest to use named table at least for table with expense entries. It manages ranges automatically for you.
=SUM((Table1[Code]=$B4)*(Table1[SubCode]=C$3)*Table1[Ammount])
Note: Stretching is not working here correctly for horizontal direction.
So use
=SUM((Table1[Code]=$C4)*(Table1[SubCode]=D$3)*Table1[Ammount])
=SUM((Table1[Code]=$D4)*(Table1[SubCode]=E$3)*Table1[Ammount])
etc for each next column