IF, LEFT, MID problem

**ktbb0312** · 04-23-2013, 12:09 PM

I've been working on this for a while and cannot get it to work.

In the attached example, I have cells E5-E35 in which the user can input various codes. The codes consist of one letter and one number (from 1-8), with the letter always appearing first. There can be one or two codes entered per cell. For example, a cell can have "V4" or "V4 P2"

In E37, I would like to add up the total values for all "V" codes (which can range from V1 to V8). Unfortunately, the "V" code can be the first or second code entered, as this will depend on the users. Thus, the "V" can either be the 1st value or the 4th value (counting the blank between the codes as a value) in the cell.

I tried the following, but keep getting the #VALUE! error.

=SUM(IF(LEFT(E$5:E$35,2)="V1",1),IF(LEFT(E$5:E$35,2)="V2",2),IF(LEFT(E$5:E$35,2)="V3",3),IF(LEFT(E$5:E$35,2)="V4",4),IF(LEFT(E$5:E$35,2)="V5",5),IF(LEFT(E$5:E$35,2)="V6",6),IF(LEFT(E$5:E$35,2)="V7",7),(IF(LEFT(E$5:E$35,2)="V8",8))+SUM(IF(MID(E$5:E$35,4,2)="V1",1),IF(MID(E$5:E$35,4,2)="V2",2),IF(MID(E$5:E$35,4,2)="V3",3),IF(MID(E$5:E$35,4,2)="V4",4),IF(MID(E$5:E$35,4,2)="V5",5),IF(MID(E$5:E$35,4,2)="V6",6),IF(MID(E$5:E$35,4,2)="V7",7),IF(MID(E$5:E$35,4,2)="V8",8)))

Thanks in advance for your help.

**djapigo** · 04-23-2013, 12:24 PM

Hi ktbb0312,

Here's what I came up with... it's an array-formula, so make sure to use array-enter (CTRL-SHIFT-ENTER) and not just ENTER... magical curly brackets will appear...

=SUM(VALUE(IFERROR(MID($E$5:$E$35,SEARCH("V",$E$5:$E$35)+1,1),0)))

Let me know if this doesn't work...

Dennis

**ktbb0312** · 04-23-2013, 12:45 PM

That is so much better. Thank you. It was driving me crazy.

**Marcol** · 04-23-2013, 12:46 PM

See if this does as you need.

In a spare column, say Column G, in G5, Drag down to G35. Then Sum(G5:G35)

Formula:

Please Login or Register  to view this content.

If this gives you the correct result, try this CSE Array, confirm with Ctrl+Shift+Enter not just Enter

Formula:

Please Login or Register  to view this content.

The helper column, Column G isn't required.

**ktbb0312** · 04-23-2013, 12:59 PM

Thanks, Marcol. That worked as well.

**Marcol** · 04-23-2013, 01:05 PM

Just as a note, my suggestion will work with 2003 and earlier.
IFERROR() is for 2007 and above, if this is not a requirement, go with Dennis ...

**ktbb0312** · 04-23-2013, 01:54 PM

Dennis - This worked well. After thinking about it some, I think it may be better to display a blank when there is an error, rather than a 0. I tried changing the 0 in your formula to "" but that did not work. Clearly, I'm missing something. Any thoughts?

Keith

**djapigo** · 04-23-2013, 02:08 PM

Hi Keith,

Instead of adding to the formula, all you need to change is the number formatting.

Go to one of the cells and check what number formatting is used. Right click, Format Cells, Number... choose Category: Custom

In Type: change it to 0;;;

Let me know if this works.

**daddylonglegs** · 04-23-2013, 02:37 PM

This is my suggestion for a single formula, using SUMPRODUCT to avoid "array entry"

=SUMPRODUCT((0&MID(E5:E35,FIND("V",E5:E35&"V")+1,1))+0)

**ktbb0312** · 04-23-2013, 02:41 PM

Thanks, Dennis. That is a good trick.

Thank you daddylonglegs - I'll give it a whirl.

IF, LEFT, MID problem

LinkBack

Thread Tools

Rate This Thread

Display

IF, LEFT, MID problem

Re: IF, LEFT, MID problem

Re: IF, LEFT, MID problem

Re: IF, LEFT, MID problem

Re: IF, LEFT, MID problem

Re: IF, LEFT, MID problem

Re: IF, LEFT, MID problem

Re: IF, LEFT, MID problem

Re: IF, LEFT, MID problem

Re: IF, LEFT, MID problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions