# SUMIF problem

#### jroule1

##### New Member
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

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### lenze

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

#### jroule1

##### New Member
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

##### Well-known Member
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 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

Last edited:

#### jroule1

##### New Member
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

##### Well-known Member

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

##### MrExcel MVP
Is it not this, where A2 is the contract (WBS) number?
Code:
``````=SUMIF('Select Contracts'!A:A,
'Finalized Data'!A2&"*",
'Select Contracts'!B:B)``````

#### nbrcrunch

##### Well-known Member
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?
Code:
``````=SUMIF('Select Contracts'!A:A,
'Finalized Data'!A2&"*",
'Select Contracts'!B:B)``````

#### jroule1

##### New Member
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

##### Well-known Member
why not try it first, then write back.

Replies
5
Views
294
Replies
9
Views
743
Replies
13
Views
623
Replies
3
Views
308
Replies
12
Views
478

1,190,693
Messages
5,982,327
Members
439,773
Latest member
tyruschen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back