SUM of Range with formulas

[mglassco]

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

[newdoverman]

I'm not sure where your vlookup table is so I made up my own. Perhaps the setup will help you solve your problem.

[mglassco]

Initial thought, I am using VLOOKUP incorrectly. Thanks I will repost with my fouled logic.

[mglassco]

I don't think it is worth trying to post my fouled logic.

Basically I already had lots of entries in my A and B column when I got the idea to try and make B auto populate. So, I put the VLOOKUP in the middle of my Column B to reference the entries I have already entered above it.

So in B50 for example, my VLOOKUP was essentially =VLOOKUP(A50,A1:B100,2) so the VLOOKUP was checking itself. I will refine my logic and see if I can work through this. My thinking was for the new entries I would just write over the VLOOKUP and input the new entry.

I then tried making my formula only look at the row above it. So in B50 I then tried =VLOOKUP(A50,A1:B49,2) but that really started giving me some really odd responses. I should note that this file is a Google Spreadsheet.

I am trying to avoid having to created the VLOOKUP table manually. Trying to have it be dynamic I guess.

[FDibbins]

Maybe this...
=VLOOKUP(A50,$A$1:$B$49,2)

[mglassco]

Awesome, thank you I got it figured out.

Using your IF statement at the beginning of my VLOOKUP returns a 0 so SUM works now. Thank you very much!

Lookup - ndm.xlsx

[FDibbins]

Glad it worked for you

[mglassco]

Maybe this...
=VLOOKUP(A50,$A$1:$B$49,2)

Thats pretty close to what I ended up with

=VLOOKUP(A50,$A$1:$B49,2) is what I ended up with so as I move down the range will continue to grow to check up until the line above it

My real problem was the SUM, and changing that to =IF(A50="","",VLOOKUP(A50,$A$1:$B49,2,)) helped as it returns a 0 until it has a valid entry which is just what I was needing.

Thanks!

[newdoverman]

Thanks for the feedback. Glad that you got it figured out.