SUM TEXT values with VLOOKUP

[Webbers]

Okay first, thanks in advance. I have been trying to work through this one, and I am sure it will come down to something simple, but I just cannot figure it out. I have a spreadsheet to track multiple projects. I am using this formula in a cell to add the values I have given to each status. the issue I am having is that it will not recognize a blank status and give it the designated value of 0. instead it will provide a value of 0 as a total, even if the correct response should be 50%. So if I have 1 item as complete, 1 as in progress and the remaining 2 are blank, 35% should be displayed, however it displays 0% instead. If I use "Not Started" for everything that has not been touched yet, I can get it to work correctly. I am attaching a sample for review. Please see cells D2, B9, and D9, which I have highlighted in yellow. none of these 3 cells provide the correct value being displayed.

Complete 25.00%
In Progress 10.00%
Not Started 0.00%
Overdue 0.00%
0.00%

=IFERROR(SUM(VLOOKUP(C3,PerDone,2,FALSE)+VLOOKUP(C4,PerDone,2,FALSE)+VLOOKUP(C5,PerDone,2,FALSE)+VLOOKUP(C6,PerDone,2,FALSE)),0)

[Moo the Dog]

For simplicity, couldn't you just put a dash "-" in cell A6 on the list page instead of leaving it completely blank, and set your validation to cover A2:A6?

- Moo

[Pete_UK]

All you need to do is put zero in cell A6 of the Lists sheet, and then the calculations will be correct.

Hope this helps.

Pete

[Richard Buttrey]

Hi

Just put a zero in A6 on the Lists sheet rather than leaving it blank.

[Webbers]

Pete & Richard, I knew it had to be something simple!!! Thanks so much!!!!!

[Pete_UK]

Glad to be able to help - thanks for the rep.

Pete

[BillyRaySpivy]

I was working on another way and attached the file. It's up to you either way. Good luck with your project!