Sum help

B

bboysen

New Member
Joined
Aug 6, 2010
Messages
44
Hi there once again.

Here is what i am trying to do.

In column W i have a comment of "BAD"
In column D i have a length
In column A i have a number

What i would like to do is have a formula that will look at column W and find the comment of "BAD", then sum the length of column d between two number that are in column A.
All Based off of the input of S1 and S2.
Example:

Excel 2010
ABCDEQRSTUVW

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Bad Seam From Mile Post to Mile Post[/TD]
[TD="align: center"]1st Mile Post[/TD]
[TD="align: center"]105[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2nd Mile Post[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]MILE POST[/TD]
[TD="align: center"]Station Number[/TD]
[TD="align: center"]Item No.[/TD]
[TD="align: center"]Pipe Length[/TD]
[TD="align: center"](Degrees)[/TD]
[TD="align: center"]File Name[/TD]
[TD="align: center"]Mill Order Number[/TD]
[TD="align: center"]Serial No[/TD]
[TD="align: center"]Mill Test PSI[/TD]
[TD="align: center"]Coating Location[/TD]
[TD="align: center"]Coating Mills[/TD]
[TD="align: center"]Comments[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]105.27[/TD]
[TD="align: center"]5558+21[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]69.2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SP2TAMP105.2GC082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007988[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]105.28[/TD]
[TD="align: center"]5558+90[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]10.0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SP2TAMP105.2GC082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007988[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]105.29[/TD]
[TD="align: center"]5559+56[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]78.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SP2TAMP105.2GC082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007990[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]105.38[/TD]
[TD="align: center"]5564+00[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]74.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SP2TAMP105.2GC082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007990[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]105.38[/TD]
[TD="align: center"]5564+00[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]46.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA105SM082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007990[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]105.39[/TD]
[TD="align: center"]5564+47[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2.7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA105SM082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007990[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"]BAD[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]105.39[/TD]
[TD="align: center"]5564+50[/TD]
[TD="align: center"][/TD]
[TD="align: center"]23.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA105SM082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007990[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]105.39[/TD]
[TD="align: center"]5564+75[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]74.9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SP2TAMP105.2GC082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007988[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]105.42[/TD]
[TD="align: center"]5566+23[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]71.1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SP2TAMP105.2GC082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007988[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]105.43[/TD]
[TD="align: center"]5566+67[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]78.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA122RB070712.xls[/TD]
[TD="align: center"]DM42550[/TD]
[TD="align: center"]C0008026[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]105.43[/TD]
[TD="align: center"]5566+94[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]68.2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SP2TAMP105.2GC082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007988[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]105.44[/TD]
[TD="align: center"]5567+00[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]62.7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA106RB071012.xls[/TD]
[TD="align: center"]DM42550[/TD]
[TD="align: center"]C0008026[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]105.44[/TD]
[TD="align: center"]5567+00[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA106RB071012.xls[/TD]
[TD="align: center"]DM42550[/TD]
[TD="align: center"]C0008026[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"]BAD[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]105.45[/TD]
[TD="align: center"]5567+62[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]67.6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SP2TAMP105.2GC082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007988[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]105.45[/TD]
[TD="align: center"]5567+67[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]57.4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA106RB071012.xls[/TD]
[TD="align: center"]DM42550[/TD]
[TD="align: center"]C0008025[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]105.45[/TD]
[TD="align: center"]5567+67[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA106RB071012.xls[/TD]
[TD="align: center"]DM42550[/TD]
[TD="align: center"]C0008025[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"]BAD[/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]105.45[/TD]
[TD="align: center"]5567+67[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6.1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA106RB071012.xls[/TD]
[TD="align: center"]DM42550[/TD]
[TD="align: center"]C0008025[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]105.46[/TD]
[TD="align: center"]5568+20[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]69.0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA122RB070712.xls[/TD]
[TD="align: center"]DM42550[/TD]
[TD="align: center"]C0008026[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]105.46[/TD]
[TD="align: center"]5568+30[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]68.1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SP2TAMP105.2GC082912[/TD]
[TD="align: center"]DM42545[/TD]
[TD="align: center"]C0007988[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]105.46[/TD]
[TD="align: center"]5568+41[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]54.3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA106RB071012.xls[/TD]
[TD="align: center"]DM42550[/TD]
[TD="align: center"]C0008026[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]105.46[/TD]
[TD="align: center"]5568+41[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]22.1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA106RB071012.xls[/TD]
[TD="align: center"]DM42550[/TD]
[TD="align: center"]C0008026[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"][/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]105.46[/TD]
[TD="align: center"]5568+41[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2.1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2TA106RB071012.xls[/TD]
[TD="align: center"]DM42550[/TD]
[TD="align: center"]C0008026[/TD]
[TD="align: center"]1910 PSI[/TD]
[TD="align: center"]DURABOND[/TD]
[TD="align: center"]14-16 MILS[/TD]
[TD="align: center"]BAD[/TD]

</tbody>
Master Tally Spread 2​
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
bboysen said:
Hi there once again.

Here is what i am trying to do.

In column W i have a comment of "BAD"
In column D i have a length
In column A i have a number

What i would like to do is have a formula that will look at column W and find the comment of "BAD", then sum the length of column d between two number that are in column A.
All Based off of the input of S1 and S2.
Example:

Excel 2010
A
B
C
D
E
Q
R
S
T
U
V
W
1
Bad Seam From Mile Post to Mile Post
1st Mile Post
105
2
2nd Mile Post
110
3
Total
4
5
MILE POST
Station Number
Item No.
Pipe Length
(Degrees)
File Name
Mill Order Number
Serial No
Mill Test PSI
Coating Location
Coating Mills
Comments
6
105.27
5558+21
1
69.2
SP2TAMP105.2GC082912
DM42545
C0007988
1910 PSI
DURABOND
14-16 MILS
7
105.28
5558+90
2
10.0
SP2TAMP105.2GC082912
DM42545
C0007988
1910 PSI
DURABOND
14-16 MILS
8
105.29
5559+56
3
78.8
SP2TAMP105.2GC082912
DM42545
C0007990
1910 PSI
DURABOND
14-16 MILS
9
105.38
5564+00
4
74.8
SP2TAMP105.2GC082912
DM42545
C0007990
1910 PSI
DURABOND
14-16 MILS
10
105.38
5564+00
1
46.8
2TA105SM082912
DM42545
C0007990
1910 PSI
DURABOND
14-16 MILS
11
105.39
5564+47
2.7
2TA105SM082912
DM42545
C0007990
1910 PSI
DURABOND
14-16 MILS
BAD
12
105.39
5564+50
23.8
2TA105SM082912
DM42545
C0007990
1910 PSI
DURABOND
14-16 MILS
13
105.39
5564+75
5
74.9
SP2TAMP105.2GC082912
DM42545
C0007988
1910 PSI
DURABOND
14-16 MILS
14
105.42
5566+23
7
71.1
SP2TAMP105.2GC082912
DM42545
C0007988
1910 PSI
DURABOND
14-16 MILS
15
105.43
5566+67
1
78.8
2TA122RB070712.xls
DM42550
C0008026
1910 PSI
DURABOND
14-16 MILS
16
105.43
5566+94
8
68.2
SP2TAMP105.2GC082912
DM42545
C0007988
1910 PSI
DURABOND
14-16 MILS
17
105.44
5567+00
2
62.7
2TA106RB071012.xls
DM42550
C0008026
1910 PSI
DURABOND
14-16 MILS
18
105.44
5567+00
2
3.8
2TA106RB071012.xls
DM42550
C0008026
1910 PSI
DURABOND
14-16 MILS
BAD
19
105.45
5567+62
9
67.6
SP2TAMP105.2GC082912
DM42545
C0007988
1910 PSI
DURABOND
14-16 MILS
20
105.45
5567+67
2
57.4
2TA106RB071012.xls
DM42550
C0008025
1910 PSI
DURABOND
14-16 MILS
21
105.45
5567+67
2
6.8
2TA106RB071012.xls
DM42550
C0008025
1910 PSI
DURABOND
14-16 MILS
BAD
22
105.45
5567+67
2
6.1
2TA106RB071012.xls
DM42550
C0008025
1910 PSI
DURABOND
14-16 MILS
23
105.46
5568+20
4
69.0
2TA122RB070712.xls
DM42550
C0008026
1910 PSI
DURABOND
14-16 MILS
24
105.46
5568+30
10
68.1
SP2TAMP105.2GC082912
DM42545
C0007988
1910 PSI
DURABOND
14-16 MILS
25
105.46
5568+41
3
54.3
2TA106RB071012.xls
DM42550
C0008026
1910 PSI
DURABOND
14-16 MILS
26
105.46
5568+41
3
22.1
2TA106RB071012.xls
DM42550
C0008026
1910 PSI
DURABOND
14-16 MILS
27
105.46
5568+41
3
2.1
2TA106RB071012.xls
DM42550
C0008026
1910 PSI
DURABOND
14-16 MILS
BAD

<TBODY>
</TBODY>
Master Tally Spread 2​
Click to expand...
When you say "between" the inputs of S1 and S2 I assume you mean:

Greater than or equal to S1 (105)
Less than S2 (110)

=SUMIFS(D6:D27,A6:A27,">="&S1,A6:A27," < "&S2,W6;W27,"Bad")
 
Last edited:
Upvote 0
Hi,

Not sure I completely follow.

Give the data provided, what result are you expecting and how is this result derived?

Matty
 
Upvote 0
T. Valko said:
When you say "between" the inputs of S1 and S2 I assume you mean:

Greater than or equal to S1 (105)
Less than S2 (110)

=SUMIFS(D6:D27,A6:A27,">="&S1,A6:A27," < "&S2,W6;W27,"Bad")
Click to expand...


You are correct on the between part but I am still getting a zero.
 
Upvote 0
Matty said:
Hi,

Not sure I completely follow.

Give the data provided, what result are you expecting and how is this result derived?

Matty
Click to expand...


What i am looking to do is get the sum of all the pipe length with the comment of bad and between a range of mile post!

hope that makes helps!
 
Upvote 0
bboysen said:
You are correct on the between part but I am still getting a zero.
Click to expand...
In the formula I posted there is a semi-colon where a comma should be.

Here is the correct formula:

=SUMIFS(D6:D27,A6:A27,">="&S1,A6:A27,"<"&S2,W6:W27,"Bad")

When I copy and paste your sample data into an Excel file the formula correctly returns 15.4.
 
Upvote 0
T. Valko said:
In the formula I posted there is a semi-colon where a comma should be.

Here is the correct formula:

=SUMIFS(D6:D27,A6:A27,">="&S1,A6:A27,"<"&S2,W6:W27,"Bad")

When I copy and paste your sample data into an Excel file the formula correctly returns 15.4.
Click to expand...




Thanks a bunch that works just how I wanted it to!

:beerchug:
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top