In Column B I have a VLOOKUP to Reference Col A to automatically populate Col B if Col A is a repeat entry (B is always tied to A so if A shows up again B is always the same as the previous entry)

My problem is when I try to sum the range in B it doesn't work I believe because of the formula


I looked to SUMIF and tried ISNUMER but that doesn't seem to be the right option as I believe it needs to reference another range.


Houston.....500
Phoenix......100
Santa Fe....300
Houston......=VLOOKUP(A4,$A$1:$B$4,2)

=SUM(B1:B4) and I would hope to get 1400 but I am getting --

How can I sum a range with Formulas in it? Thanks