cmondeau
Board Regular
- Joined
- Sep 23, 2014
- Messages
- 86
Hello again!
I am trying to count the corresponding quantities of each pipe according to their sizes and lengths. (the last table on the right)
I've tried recording =countifs() in the worksheet, but am having a hard time converting it with dynamic criteria. Here is the formula for cell K2 (below 1/2" and to the right of Stock A):
What I am trying to change in this formula is to have the range1 be something on the lines of ($B$2:$B & r,$G$2,$C$2:$C & r,$H$2).
I'm creating a table in a new sheet for each size of pipe, so I am trying to count the quantities of each length of pipe for that corresponding length. Sheet 2 will have the quantity of the lengths of pipe for 1/2", sheet 3 will have the quantity of the lengths of pipe for 3/4", etc.
Any help or suggestions?
Thanks!
I am trying to count the corresponding quantities of each pipe according to their sizes and lengths. (the last table on the right)
Code:
[TABLE="width: 946"]
<tbody>[TR]
[TD]Item No[/TD]
[TD]Pipe Size[/TD]
[TD]Length[/TD]
[TD]Description[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD]Unique Pipe Size[/TD]
[TD]Unique Length[/TD]
[TD][/TD]
[TD]Quantity[/TD]
[TD]1/2"[/TD]
[TD]3/4"[/TD]
[TD]1"[/TD]
[/TR]
[TR]
[TD="align: right"]1284[/TD]
[TD]0 1/2"[/TD]
[TD]2"[/TD]
[TD]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD]Straights[/TD]
[TD][/TD]
[TD]0 1/2"[/TD]
[TD]2"[/TD]
[TD][/TD]
[TD]Stock A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1252[/TD]
[TD]0 1/2"[/TD]
[TD]2"[/TD]
[TD]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD]Straights[/TD]
[TD][/TD]
[TD]0 3/4"[/TD]
[TD]3"[/TD]
[TD][/TD]
[TD]Stock B[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1162[/TD]
[TD]0 1/2"[/TD]
[TD]2"[/TD]
[TD]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD]Straights[/TD]
[TD][/TD]
[TD]1"[/TD]
[TD]7"[/TD]
[TD][/TD]
[TD]Stock C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1429[/TD]
[TD]0 3/4"[/TD]
[TD]3"[/TD]
[TD]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD]Straights[/TD]
[TD][/TD]
[TD][/TD]
[TD]12"[/TD]
[TD][/TD]
[TD]Stock D[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1276[/TD]
[TD]0 3/4"[/TD]
[TD]2"[/TD]
[TD]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD]Straights[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1269[/TD]
[TD]0 3/4"[/TD]
[TD]3"[/TD]
[TD]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD]Straights[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1251[/TD]
[TD]1"[/TD]
[TD]7"[/TD]
[TD]A53 CW Sch 40 Blk T&C[/TD]
[TD]Straights[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1157[/TD]
[TD]1"[/TD]
[TD]12"[/TD]
[TD]A53 CW Sch 40 Blk T&C[/TD]
[TD]Straights[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1247[/TD]
[TD]1"[/TD]
[TD]12"[/TD]
[TD]A53 CW Sch 40 Blk T&C[/TD]
[TD]Straights[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've tried recording =countifs() in the worksheet, but am having a hard time converting it with dynamic criteria. Here is the formula for cell K2 (below 1/2" and to the right of Stock A):
Code:
=COUNTIFS($B$2:$B$10,$G$2,$C$2:$C$10,$H2)
What I am trying to change in this formula is to have the range1 be something on the lines of ($B$2:$B & r,$G$2,$C$2:$C & r,$H$2).
I'm creating a table in a new sheet for each size of pipe, so I am trying to count the quantities of each length of pipe for that corresponding length. Sheet 2 will have the quantity of the lengths of pipe for 1/2", sheet 3 will have the quantity of the lengths of pipe for 3/4", etc.
Any help or suggestions?
Thanks!