Finding range of partial match hits

[wasabi]

I have a table containing a column of names in the form (V1a, V1b, V1c, V2, V3a, V4a, always in that sort of order). Next to this column is another containing number values. I want to sum all the values belonging to the same name (V1, V2 or V3).

That is, I want to sum the values beside "V1a", "V1b" and "V1c".

However, I've tried using SUMIF, couldn't figure out how to formulate it for this situation. I tried using MATCH, couldn't figure out how to formulate it in this situation. So now I ask you guys for assistance.

This would probably be simple enough to do in VBA, but I'd rather avoid that if possible.

 

[Andrew Poulsom]

Did you try?

=SUMIF(A:A,"V1*",B:B)

 

[wasabi]

I tried that and it does kind of work, however that also catches any values from "V10a", for example.

Is there a wildcard only for characters, not numbers? The only wildcards I know of are "*" and "?". "V1?" actually works better, but it still catches a case like "V10".

 

[Andrew Poulsom]

I tried that and it does kind of work, however that also catches any values from "V10a", for example.

Is there a wildcard only for characters, not numbers? The only wildcards I know of are "*" and "?". "V1?" actually works better, but it still catches a case like "V10".

Sorry, no.

 

[wasabi]

Figured as much. Hell, numbers are characters as far as ASCII is concerned, so I'm not surprised.

Does this mean I'd need some VBA to get this done?

 

[Matty]

Not tested exhaustively, but seems to work on the examples posted:

=SUM(IF(LEFT(A$2:A$6,LEN(C2))=C2,IF(1-ISNUMBER(MID(A$2:A$6,LEN(C2)+1,1)+0),B$2:B$6)))

Array entered. Change ranges to suit.

Matty

 

[Matty]

Apologies, I should have detailed my data layout so you could understand what was going on. As follows:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 12px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Names</TD><TD>Values</TD><TD></TD><TD>Names</TD><TD>Values</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>V1</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD>V1</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>V10b</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD>V2</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>V1b</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD>V20</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>V2</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD>V10</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>V20a</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
Array formula in E2 copied down is:

=SUM(IF(LEFT(A$2:A$6,LEN(D2))=D2,IF(1-ISNUMBER(MID(A$2:A$6,LEN(D2)+1,1)+0),B$2:B$6)))

Matty