Countifs with dynamic criterai in VBA

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)

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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
Sub dynamicCountIf()

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Cells(2, 9).FormulaR1C1 = "=COUNTIFS(R2C2:R" & lastRow & "C2, R2C7,R2C3:R" & lastRow & "C3,RC8)"
End Sub
 

cmondeau

Board Regular
Joined
Sep 23, 2014
Messages
86
Thanks for the quick reply! Unfortunately, it doesn't appear to be getting the correct values, nor update when additional data is entered.

This table is what I'm trying to get. I've tried solving for only one size length of pipe (1/2") and the various lengths, but to no avail.
Code:
[TABLE="width: 128"]
 <colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl65, width: 64"]Length[/TD]
  [TD="width: 64"]1/2"[/TD]
 [/TR]
 [TR]
  [TD]2"[/TD]
  [TD="align: right"]3[/TD]
 [/TR]
 [TR]
  [TD]3"[/TD]
  [TD="align: right"]0[/TD]
 [/TR]
 [TR]
  [TD]7"[/TD]
  [TD="align: right"]0[/TD]
 [/TR]
 [TR]
  [TD]12"[/TD]
  [TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Thanks for the quick reply! Unfortunately, it doesn't appear to be getting the correct values, nor update when additional data is entered.

This table is what I'm trying to get. I've tried solving for only one size length of pipe (1/2") and the various lengths, but to no avail.

I just copied your countif code and turned it into a relative reference. Maybe you mean this?

Code:
Sub dynamicCountIf()

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(2, 11), Cells(5, 13)).FormulaR1C1 = _
    "=COUNTIFS(R2C2:R" & lastRow & "C2, R1C,R2C3:R" & lastRow & "C3,RC8)"


End Sub

As for updating with new data, the macro needs to be rerun. Or you can put it into a worksheet_change event.
 

cmondeau

Board Regular
Joined
Sep 23, 2014
Messages
86
Not quite. I'll have a variation of lengths added, so I'm trying to count the quantities of each length in the specific pipe size.

For example, if I run one macro with this data, I should get the following results:
Code:
[TABLE="width: 448"]
 <colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl65, width: 64"]Item No[/TD]
  [TD="class: xl65, width: 64"]Pipe Size[/TD]
  [TD="class: xl65, width: 64"]Length[/TD]
  [TD="class: xl65, width: 64"]Description[/TD]
  [TD="class: xl65, width: 64"]Type[/TD]
  [TD="class: xl65, width: 64"]Pipe Size[/TD]
  [TD="class: xl65, width: 64"]Length[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1284[/TD]
  [TD="class: xl65"]0 1/2"[/TD]
  [TD="class: xl65"]2"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD]0 1/2"[/TD]
  [TD]2"[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1252[/TD]
  [TD="class: xl65"]0 1/2"[/TD]
  [TD="class: xl65"]2"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD]0 3/4"[/TD]
  [TD]3"[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1162[/TD]
  [TD="class: xl65"]0 1/2"[/TD]
  [TD="class: xl65"]2"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD]1"[/TD]
  [TD]7"[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1429[/TD]
  [TD="class: xl65"]0 3/4"[/TD]
  [TD="class: xl65"]3"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD]12"[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1276[/TD]
  [TD="class: xl65"]0 3/4"[/TD]
  [TD="class: xl65"]2"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1269[/TD]
  [TD="class: xl65"]0 3/4"[/TD]
  [TD="class: xl65"]3"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1251[/TD]
  [TD="class: xl65"]1"[/TD]
  [TD="class: xl65"]7"[/TD]
  [TD="class: xl65"]A53 CW Sch 40 Blk T&C[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1157[/TD]
  [TD="class: xl65"]1"[/TD]
  [TD="class: xl65"]12"[/TD]
  [TD="class: xl65"]A53 CW Sch 40 Blk T&C[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1247[/TD]
  [TD="class: xl65"]1"[/TD]
  [TD="class: xl65"]12"[/TD]
  [TD="class: xl65"]A53 CW Sch 40 Blk T&C[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
[/TR]
</tbody>[/TABLE]

But say if I add 2 additional pipes (both 1/2" thick, but one is 2" long, and the other is 5" long), I would get the following report:
Code:
[TABLE="width: 512"]
 <colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl65, width: 64"]Item No[/TD]
  [TD="class: xl65, width: 64"]Pipe Size[/TD]
  [TD="class: xl65, width: 64"]Length[/TD]
  [TD="class: xl65, width: 64"]Description[/TD]
  [TD="class: xl65, width: 64"]Type[/TD]
  [TD="class: xl65, width: 64"]Pipe Size[/TD]
  [TD="class: xl65, width: 64"]Length[/TD]
  [TD="width: 64"]1/2"[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1284[/TD]
  [TD="class: xl65"]0 1/2"[/TD]
  [TD="class: xl65"]2"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD]0 1/2"[/TD]
  [TD]2"[/TD]
  [TD="align: right"]4[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1252[/TD]
  [TD="class: xl65"]0 1/2"[/TD]
  [TD="class: xl65"]2"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD]0 3/4"[/TD]
  [TD]3"[/TD]
  [TD="align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1162[/TD]
  [TD="class: xl65"]0 1/2"[/TD]
  [TD="class: xl65"]2"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD]1"[/TD]
  [TD]7"[/TD]
  [TD="align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1429[/TD]
  [TD="class: xl65"]0 3/4"[/TD]
  [TD="class: xl65"]3"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD]12"[/TD]
  [TD="align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1276[/TD]
  [TD="class: xl65"]0 3/4"[/TD]
  [TD="class: xl65"]2"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD="class: xl65"]5"[/TD]
  [TD="align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1269[/TD]
  [TD="class: xl65"]0 3/4"[/TD]
  [TD="class: xl65"]3"[/TD]
  [TD="class: xl65"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1251[/TD]
  [TD="class: xl65"]1"[/TD]
  [TD="class: xl65"]7"[/TD]
  [TD="class: xl65"]A53 CW Sch 40 Blk T&C[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1157[/TD]
  [TD="class: xl65"]1"[/TD]
  [TD="class: xl65"]12"[/TD]
  [TD="class: xl65"]A53 CW Sch 40 Blk T&C[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1247[/TD]
  [TD="class: xl65"]1"[/TD]
  [TD="class: xl65"]12"[/TD]
  [TD="class: xl65"]A53 CW Sch 40 Blk T&C[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]1234[/TD]
  [TD="class: xl65"]0 1/2"[/TD]
  [TD="class: xl65"]2"[/TD]
  [TD="class: xl65"]A53 CW Sch 40 Blk T&C[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]4321[/TD]
  [TD="class: xl65"]0 1/2"[/TD]
  [TD="class: xl65"]5"[/TD]
  [TD="class: xl65"]A53 CW Sch 40 Blk T&C[/TD]
  [TD="class: xl65"]Straights[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
[/TR]
</tbody>[/TABLE]


So it's my lengths as a criteria that are changing.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,040
Messages
5,526,412
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top