COUNTIF Dynamic Range Formula

[Gos-C]

Hi all,

I have the following formula in cell EG1:

=COUNTIF$EG2:$EG550,"a")

but I need to use a dynamic range instead of the fixed range.

I tried:

=COUNTIF(OFFSET($EG2,0,0,COUNTA($EG:$EG),1),"a")

and

=COUNTA(OFFSET($EG2,0,0,MATCH(1E+306,$EG:$EG),"a")

but neither works.

Any help, please?

Thank you,
Gos-C

 

[id107]

This may help if all cells are filled
=COUNTIF(INDIRECT("EG2:EG"&COUNTA(EG:EG)),"a")

 

[Marcelo Branco]

Maybe this

=COUNTIF($EG$2:INDEX($EG:$EG,MATCH(REPT("z",255),$EG:$EG)),"a")

HTH

M.

 

[T. Valko]

Hi all,

I have the following formula in cell EG1:

=COUNTIF$EG2:$EG550,"a")

Maybe this

=COUNTIF($EG$2:INDEX($EG:$EG,MATCH(REPT("z",255),$EG:$EG)),"a")

HTH

M.

As written, that formula will produce a circular reference.

=COUNTIF(EG2:INDEX(EG2:EG65536,MATCH("zzzzz",EG2:EG65536)),"a")

However, I have to ask if a dynamic range is really necessary? Why do they need a dynamic range with a COUNTIF function?

 

[Marcelo Branco]

As written, that formula will produce a circular reference.

Oh yes ...., i overlooked that the formula is being used in EG1.
Tks for pointing this out

M.

 

[Gos-C]

Thank you, Marcelo, id107 and T. valko.

Yes, I saw the circular reference; I move the formula to cell EH1.

=COUNTIF(INDIRECT("EG2:EG"&COUNTA(EG:EG)),"a") did not give the correct answer.

I perfer =COUNTIF($EG$2:INDEX($EG:$EG,MATCH(REPT("z",255),$EG:$EG)),"a").

I am using a dynamic range here because I also used it in column A to determine the data range.

With appreciation,
Gos-C

 

[Gos-C]

Hi all,

I want to modify the following code to allow the user to select EG only if EE has PAID:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

        If Not Intersect(Target, Range("EG2:EG" & Cells(Rows.Count, 1).End(xlUp).Row)) Is Nothing Then

            Target.Font.Name = "Marlett"

                If Target = vbNullString Then

                    Target = "a"

                Else

                    Target = vbNullString

                End If
        End If

End Sub

Any help, please?

Thank you,
Gos-C

 

[Gos-C]

Hi all,

I have started a new thread for the VBA question:

http://www.mrexcel.com/forum/showthread.php?p=2803947#post2803947

Gos-C