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
1Bad Seam From Mile Post to Mile Post1st Mile Post105
22nd Mile Post110
3Total0
4
5MILE POSTStation NumberItem No.Pipe Length(Degrees)File NameMill Order NumberSerial NoMill Test PSICoating LocationCoating MillsComments
6105.275558+21169.2SP2TAMP105.2GC082912DM42545C00079881910 PSIDURABOND14-16 MILS
7105.285558+90210.0SP2TAMP105.2GC082912DM42545C00079881910 PSIDURABOND14-16 MILS
8105.295559+56378.8SP2TAMP105.2GC082912DM42545C00079901910 PSIDURABOND14-16 MILS
9105.385564+00474.8SP2TAMP105.2GC082912DM42545C00079901910 PSIDURABOND14-16 MILS
10105.385564+00146.82TA105SM082912DM42545C00079901910 PSIDURABOND14-16 MILS
11105.395564+472.72TA105SM082912DM42545C00079901910 PSIDURABOND14-16 MILSBAD
12105.395564+5023.82TA105SM082912DM42545C00079901910 PSIDURABOND14-16 MILS
13105.395564+75574.9SP2TAMP105.2GC082912DM42545C00079881910 PSIDURABOND14-16 MILS
14105.425566+23771.1SP2TAMP105.2GC082912DM42545C00079881910 PSIDURABOND14-16 MILS
15105.435566+67178.82TA122RB070712.xlsDM42550C00080261910 PSIDURABOND14-16 MILS
16105.435566+94868.2SP2TAMP105.2GC082912DM42545C00079881910 PSIDURABOND14-16 MILS
17105.445567+00262.72TA106RB071012.xlsDM42550C00080261910 PSIDURABOND14-16 MILS
18105.445567+0023.82TA106RB071012.xlsDM42550C00080261910 PSIDURABOND14-16 MILSBAD
19105.455567+62967.6SP2TAMP105.2GC082912DM42545C00079881910 PSIDURABOND14-16 MILS
20105.455567+67257.42TA106RB071012.xlsDM42550C00080251910 PSIDURABOND14-16 MILS
21105.455567+6726.82TA106RB071012.xlsDM42550C00080251910 PSIDURABOND14-16 MILSBAD
22105.455567+6726.12TA106RB071012.xlsDM42550C00080251910 PSIDURABOND14-16 MILS
23105.465568+20469.02TA122RB070712.xlsDM42550C00080261910 PSIDURABOND14-16 MILS
24105.465568+301068.1SP2TAMP105.2GC082912DM42545C00079881910 PSIDURABOND14-16 MILS
25105.465568+41354.32TA106RB071012.xlsDM42550C00080261910 PSIDURABOND14-16 MILS
26105.465568+41322.12TA106RB071012.xlsDM42550C00080261910 PSIDURABOND14-16 MILS
27105.465568+4132.12TA106RB071012.xlsDM42550C00080261910 PSIDURABOND14-16 MILSBAD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Master Tally Spread 2​
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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!

(y)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,823
Messages
6,121,779
Members
449,049
Latest member
greyangel23

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