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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.