SUMIF problem

[jroule1]

I have a column of contract numbers of varying length. I want to run a sumif of all of the values on my reference sheet that have the contract number beginning with the contract number in my listing. All of the the contract values in my reference sheet are very long. Since the numbers I am working with vary in length, i don't know how to match this string in the sumif function

I want something like this

=SUMIF('Reference Sheet!$A$5:$A$13410,LEFT("", LEN('Select Contracts'!$C6))='Select Contracts'!$C6,'FINALIZED DATA'!$H$5:$H$13410)

Is there a way to refer to the range in the sumif function, I think the "" is where I am having the problem

 

[lenze]

Hi and welcome to the board
Can you post some example numbers and the result you expect?
lenze

 

[jroule1]

The sheet I want populated, there are many more Numbers and lengths of said numbers.

<TABLE style="WIDTH: 156pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=208 border=0 x:str><COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl24 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699 0.5pt solid; BORDER-LEFT: #666699 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ccccff" width=104 height=17>WBS</TD><TD class=xl25 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699 0.5pt solid; BORDER-LEFT: #666699; WIDTH: 78pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: #ccccff" width=104>Total</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 78pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=104 border=0 x:str><COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>3100370.AA90</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>3100370.AA91</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>3100370.AB21</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>3100370.AB47</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>3101755.1F06</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>3101881.01A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>3101881.01X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>3101881.02X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>3101881.06X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104 height=17>3101886.0.01.0002</TD></TR></TBODY></TABLE>

I want to sum all totals that begin with those numbers from the reference sheet.

<TABLE style="WIDTH: 256pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=341 border=0 x:str><COLGROUP><COL style="WIDTH: 173pt; mso-width-source: userset; mso-width-alt: 8411" width=230><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 173pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=230 height=17>Project ID</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 83pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=111>Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100006.005.01.02</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="2330"> 2,330.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100067.0A3E.00.00.00.04.6.AA</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="-3603.87"> (3,603.87)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100067.0A5T.00.00.00.04.6.AA</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="4349.49"> 4,349.49 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100067.0A71.00.00.00.06.6.SB</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="26116.5"> 26,116.50 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100139.3.01.01.001.0001.02.01</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="2748.21"> 2,748.21 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100156.0506.00.007.AC.00.09.0</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="15960.95"> 15,960.95 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100156.0525.00.010.AA.00.01.0</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="27800"> 27,800.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100156.0525.00.010.AU.00.01.0</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="224556.61"> 224,556.61 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100156.0527.00.005.AA.00.09.0</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="10921.21"> 10,921.21 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100156.0527.00.005.AC.00.09.0</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="9773.85"> 9,773.85 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100156.0527.00.005.AD.00.09.0</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1402.33"> 1,402.33 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3100156.0527.00.006.AD.00.01.0</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="19562.51"> 19,562.51 </TD></TR></TBODY></TABLE>

Does this help? Sorry I am a newb

 

[nbrcrunch]

I was going through your formula and realized you reference 3 different sheets:

Reference Sheet
Select Contracts
FINALIZED DATA

I was expecting only two:
Your Source data with amounts
Your Summary tab of unique contract numbers.

Not sure now what is what. However, what I started to prepare for you was a sumproduct formula. Take a look at this example:
http://www.simple-excel.com/excel-3-7-formula-sumproduct.htm

 

[jroule1]

No there are only two data sheets.

Finalized Data - source data with all contract numbers, Select Contracts with unique partial contract numbers for which I need the sum of all contract numbers that begin with this unique number.

 

[nbrcrunch]

Read the tutorial (from the link) I provided.

Assuming that Finalized Data column A is Contract number & column B is amounts
Assuming that the above data starts on row 2.
Assuming that Select Contracts is the same setup

put the following B2
=sumproduct((left('FINALIZED DATA'!A2:A14000,len('Select Contracts'!$A2))*('FINALIZED DATA'!B2:B14000))

copy down

 

[Colin Legg]

Is it not this, where A2 is the contract (WBS) number?

=SUMIF('Select Contracts'!A:A,
    'Finalized Data'!A2&"*",
    'Select Contracts'!B:B)

 

[nbrcrunch]

Colin: I've gotten so used to using SUMPRODUCT that I must admit this simple solution didn't even enter my mind. embarrassing.

It also dawned on me that my formula was incomplete. Should have been:
=sumproduct((left('FINALIZED DATA'!A2:A14000,len('Select Contracts'!$A2)='Select Contracts'!$A2)*('FINALIZED DATA'!B2:B14000))

Is it not this, where A2 is the contract (WBS) number?

=SUMIF('Select Contracts'!A:A,
    'Finalized Data'!A2&"*",
    'Select Contracts'!B:B)

 

[jroule1]

But won't this search for the string anywhere in the cells in the range? I was trying to use the Left function on the range of contract numbers since I need it to begin with the criteria value.

 

[nbrcrunch]

why not try it first, then write back.