Loop through range and count based on two variables

Question

I'm trying to develop a code that will allow me to loop through a range and create a total count of rows that have 2 criteria.

For the example below I want to loop through rows 2-12 and increase the counter each time a row has the words "Pway" & "T-4". Then place the result in cell G2. I've played around with loops before but never with two variables.

Can anybody help?

If AnswerToAboveComment = "Yes" Then you can nest 2 IF...Then statements to check for 1 criteria and if met, then check the 2nd, if met increase a counter variable. — Samuel Everson
– Samuel Everson, Commented Apr 22, 2021 at 12:39

FaneDuru · Accepted Answer · 2021-04-22 13:15:46Z

2

Please, try the VBA solution, too:

Sub countTwoCriteria()
 Dim sh As Worksheet, arr, lastR As Long, i As Long, count As Long
 
 Set sh = ActiveSheet
 lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
 
 arr = sh.Range("A2:D" & lastR).value
 
 For i = 1 To UBound(arr)
    If UCase(arr(i, 1) & arr(i, 4)) = UCase("Pway" & "T-4") Then count = count + 1
 Next i
 If count > 0 Then sh.Range("G2").value = count
End Sub

answered Apr 22, 2021 at 13:15

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pᴇʜ · Accepted Answer · 2021-04-22 12:42:34Z

0

Add the following formula in G2 and pull it down

=COUNTIFS($A$2:A2,"Pway",$D$2:D2,"T-4")

To get the following result:

answered Apr 22, 2021 at 12:42

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Collectives™ on Stack Overflow

Loop through range and count based on two variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related