Solved: udf: countif for multiple ranges and sigle or multiple criteria

[mancubus]

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]

Why do you need a UDF, what is wrong with the formula?

[mancubus]

thanks for replying.

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

[Bob Phillips]

Can you give an actual example then, it will probably affect the formula given.

[mancubus]

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]

I would use this modified UDF

[vba]

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
[/vba]

[mancubus]

thanks for your time.