Solved: Countif formula to be used on a dynamic range to be used on a data table

[bananatang]

HI,

I would appreciate some assistance in creating a macro that would allow me to use a countif formula but on a dynamic range of data in a table.

I would also like to use this macro to perform the same function however on a differant tables in the same worksheet.

I have attached a copy of a worksheet with the data/layout showing.

All help would be greatly apprecaited.

Thanks

BT

[Benzadeus]

Function CountIfDynamic(intExclusions As Range)

Const rowInitialBD = 33

Dim rngBD As Range

Set rngBD = Range(Cells(rowInitialBD, intExclusions.Column + 1), _
Cells(Cells(rowInitialBD, intExclusions.Column + 1).End(xlDown).Row - 1, intExclusions.Column + 1))

CountIfDynamic = WorksheetFunction.CountIf(rngBD, intExclusions)

Set rngBD = Nothing

End Function

[Paul_Hossler]

Not a macro like Benzadeus, but I defined 5 dynamic ranges

e.g Primary =OFFSET(Sheet1!$D$33,0,0,2000,1)

Hardcoded the 2000, but you can change that

and just used COUNTIF and the range

e.g. =COUNTIF(Primary,$C11)


BTW - i noticed that some numbers didn't close: 38 + 17 + 246 = 301, not the 299 in your 'All' for Excl = 1

Since your data seems like it will keep growing over time, you might see if Pivot Tables would be easier to maintain

Paul

[Benzadeus]

Well, you said macro =D

Otherwise... why don't use =COUNTIF($D$33:$D$65536,C11) and paste to all yellow cells?

[bananatang]

Function CountIfDynamic(intExclusions As Range)

Const rowInitialBD = 33

Dim rngBD As Range

Set rngBD = Range(Cells(rowInitialBD, intExclusions.Column + 1), _
Cells(Cells(rowInitialBD, intExclusions.Column + 1).End(xlDown).Row - 1, intExclusions.Column + 1))

CountIfDynamic = WorksheetFunction.CountIf(rngBD, intExclusions)

Set rngBD = Nothing

End Function

Hi Benzadeus (http://www.vbaexpress.com/forum/member.php?u=19707)

Thank you for your code. can you tell me what location should the code go. i am not sure if is should be in the worksheet, private module etc.
I tried these location but not sure what should have happened. i would like to know if this code can do what i need, in the mean time, thanks to Paul for his input on using a dynamic range.

Thanks

BT

[Paul_Hossler]

This is a User Defined Funtion (UDF) and should go into a regular module

If you put the module in the workbook, you can call it like any build in function.

If you put it in another WB (e.g. PERSONAL.XLS), then you need to preface it with the WB name


If you do want to use a macro (nothing wrong there -- this is the VBA forum), a slight change to consider:


Option Explicit
Function CountIfDynamic(numExclusions As Long, intExclusions As Range) As Long
Dim rngBD As Range

On Error GoTo NiceExit
Set rngBD = intExclusions.Cells(1, 1)
Set rngBD = Range(rngBD, rngBD.End(xlDown))

CountIfDynamic = Application.WorksheetFunction.CountIf(rngBD, numExclusions)

Exit Function

NiceExit:
CountIfDynamic = 0

End Function


Paul

[bananatang]

Hi Paul,

Thank you for your updated code and for the clarification.

Much appreciated.

BT

[bananatang]

This is a User Defined Funtion (UDF) and should go into a regular module

If you put the module in the workbook, you can call it like any build in function.

If you put it in another WB (e.g. PERSONAL.XLS), then you need to preface it with the WB name


If you do want to use a macro (nothing wrong there -- this is the VBA forum), a slight change to consider:


Option Explicit
Function CountIfDynamic(numExclusions As Long, intExclusions As Range) As Long
Dim rngBD As Range

On Error GoTo NiceExit
Set rngBD = intExclusions.Cells(1, 1)
Set rngBD = Range(rngBD, rngBD.End(xlDown))

CountIfDynamic = Application.WorksheetFunction.CountIf(rngBD, numExclusions)

Exit Function

NiceExit:
CountIfDynamic = 0

End Function


Paul

Hi Paul,

Could you be so kind to provide me with the vba code for a sumif formula in line with the (UDF) countif vba code you created.

I have attached a copy of the revised data which now included the new column of information.

I have tried to amend your countif code and use it as a sumif but i dont seem to get the result i am looking for.

Many Thanks for all your help.

BT

[Bob Phillips]

Function SumIfDynamic(CriteriaRange As Range, Criteria As Variant, Optional SumRange As Range) As Long
Dim rngCriteria As Range
Dim rngSum As Range

On Error GoTo NiceExit
Set rngCriteria = CriteriaRange.Cells(1, 1)
Set rngCriteria = Range(rngCriteria, rngCriteria.End(xlDown))

If SumRange Is Nothing Then

Set rngSum = rngCriteria
Else

Set rngSum = SumRange.Cells(1, 1)
Set rngSum = Range(rngSum, rngSum.End(xlDown))
End If

SumIfDynamic = Application.WorksheetFunction.SumIf(rngCriteria, Criteria, rngSum)

Exit Function

NiceExit:
SumIfDynamic = 0

End Function

[bananatang]

Hi Xld,

Thank you very much for the vba code for the sumif UDF.

I have tried and tested the formula and it works well, however it seems to round down the final sum value. I.e If a sumif total is 78.5 it will show a result of 78.

Is there a bit of code that will ensure the value will be shown to 2 decimal points.

Thanks in advance.

BT

[Benzadeus]

Maybe it is because de number cell formatting?

[bananatang]

Maybe it is because de number cell formatting?

Hi Benzadeus,

I have checked the formatting of the cells and all are set a 2 decimal places.

If i use the normal count if formula, i get the correct answer which included the 2 decimal points.

I thought by adding + 0.01 at the end of the following snippet of code would have sorted it out but it still rounds up.
SumIfDynamic = Application.WorksheetFunction.SumIf(rngCriteria, Criteria, rngSum) + 0.01

BT