PDA

View Full Version : Solved: udf: countif for multiple ranges and sigle or multiple criteria



mancubus
01-08-2011, 06:33 AM
hi board.

i need a udf for counting values in non-contiguous ranges.

single_crit_value = CountIf(Range1, CritRange1) + CountIf(Range2, CritRange1) + ... + (RangeN, Crit1)

or

multi_crit_value = CountIf(Range1, CritRange1) + CountIf(Range1, CritRange2) + ... + (RangeN, CritRange7)

is that possible?

Bob Phillips
01-08-2011, 08:07 AM
Why do you need a UDF, what is wrong with the formula?

mancubus
01-08-2011, 08:36 AM
thanks for replying.

formula is OK. i want to use one formula instead of formula1 + formula2 etc.

Bob Phillips
01-08-2011, 05:40 PM
Can you give an actual example then, it will probably affect the formula given.

mancubus
01-09-2011, 09:52 AM
thx again xld.

attached is a representative sample of my cese.

i adopted a udf from ozgrid which counts the values which are "equal" to criteria in 2-5 ranges.

the problem is with comparison operators, since the logic is result = result+1.

Bob Phillips
01-09-2011, 04:05 PM
I would use this modified UDF



Function CountIf_MR(crit, ParamArray rng())
Dim vResult As Long

For i = LBound(rng) To UBound(rng)

vResult = vResult + Application.CountIf(rng(i), crit)
Next i

CountIf_MR = vResult

End Function

mancubus
01-10-2011, 02:33 AM
:creator:
thanks for your time.

:beerchug: