COUNTIF with 2 Conditions met UNTIL 2 Condition met

**DnDXan** · 07-26-2012, 07:02 PM

Hello, this is my first post to the forum. Thank you in advance for any help you can give me.

I'm coding with VBA

Tables and Formula provided are for reference only.

What I need is to count the Cells in Column C that are greater than 1 For Items PName and ANAME(X,Y,Z).
My problem is that my number of rows for each ANAME(X,Y,Z) will vary and are NOT always in the same range/cell reference.
(e.g ANAMEX may be in spot where ANAMEZ is and vise-versa).

Something like COUNTIF/UNTIL code. Maybe?

The folowing EXAMPLE works IF my references were constant, but they are not.

Formula:

=IF(A1="PName",IF(ISERROR(FIND(B1,"X",1))=TRUE,COUNTIF(C3:C7,">1"),""),"")

Column A	Column B	Column C
PName	ANAMEX
#'s	Text	10
#'s	Text	10
#'s	Text	10
#'s	Text	10
#'s	Text	10
PName	ANAMEY
#'s	Text	10
#'s	Text	10
#'s	Text	10
#'s	Text	10
#'s	Text	10
PName	ANAMEZ
#'s	Text	10
#'s	Text	10
#'s	Text	10
#'s	Text	10
#'s	Text	10

Totals Area

	Column E	Column F
Row 2	X	Should Count to 5
Row 3	Y	Should Count to 5
Row 4	Z	Should Count to 5

I hope I explained myself properly. Thank you.

**FDibbins** · 07-26-2012, 08:25 PM

before i offer any other suggestions/help on this, take a look at the countifS() function (note the S). this may give you what you need, as it allows counting based on multiple criteria

**DnDXan** · 07-27-2012, 01:00 AM

Originally Posted by FDibbins

before i offer any other suggestions/help on this, take a look at the countifS() function (note the S). this may give you what you need, as it allows counting based on multiple criteria

I have tried COUNTIFS, however it does not work the way i need it too because my cell references are not constant. Thank You.

---------- Post added 07-27-2012 at 12:00 AM ---------- Previous post was 07-26-2012 at 11:59 PM ----------

rylo, I will give this a try and let you know. Thank You.

**rylo** · 07-26-2012, 08:36 PM

Hi

Clunky, but should get you there

Sub aaa()
  For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1) = "PName" Then
      If Len(itemm) = 1 Then
        outrow = Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row
        Cells(outrow, "E").Value = itemm
        Cells(outrow, "F").Value = cntr
      End If
      
      itemm = Right(Cells(i, 2), 1)
      cntr = 0
    End If
    If Cells(i, 3) > 1 Then cntr = cntr + 1
  Next i
  If Len(itemm) = 1 Then
    outrow = Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row
    Cells(outrow, "E").Value = itemm
    Cells(outrow, "F").Value = cntr
  End If
End Sub

rylo