Excel: Count same number values in noncontiguous range

Question

I'm looking for the most elegant way to count the same number values in a noncontiguous range (I'll refer to it as just 'range'). This is the range:

=$C$2:$C$31,$E$2:$E$31,$G$2:$G$31,$I$2:$I$31,$K$2:$K$31,$M$2:$M$31,$O$2:$O$31,$Q$2:$Q$31,$S$2:$S$7

These are the parameters:

The range contains non-adjacent columns.
The columns differ in height.
The cells in the range are either empty or contain integers.
I'm checking for how many cells equal '1', how many equal '2' etc. in the range. (Not in one go, but in seperate formulas).
I've used a named range to reference the range. I'd really like to use this named range in the formula, in one way or another.

I hope I've given you enough info... Thanks in advance!

chris neilsen · Accepted Answer · 2012-06-16 23:01:06Z

4

I agree with Kartik that a VBA solution is required. However the solution offered is a little inefficient in that it loops over every cell in the ranged passed into the function. It also limits the key parameter to a range reference, and can only count up to 32767 matches. Here's an alternative addresses these shortcomings

Function CountIf_N(rng As Range, key As Variant) As Variant
    Dim r As Range
    Dim count As Long
    count = 0
    For Each r In rng.Areas
        count = count + WorksheetFunction.CountIfs(r, key)
    Next
    CountIf_N = count
End Function

Note: assumes Excel 07 or later. If using with an ealier version replace CountIfs with CountIf

answered Jun 16, 2012 at 23:01

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xxxRxxx Over a year ago

How do you format the non-contiguous range when putting it into the function?

Kartik Anand · Accepted Answer · 2012-06-16 16:53:23Z

1

One approach is to use excel built in function Countif, but it won't work with non-contigous range. The other way (the easy way) will be to use VBA to create your own custom function, and then use it in excel.
I've presented that technique here.

Goto visual basic editor in excel by pressing Alt+F11, in the project window insert a new module and paste the below code:

Function countif_n(rng As Range, key As Range) As Integer

Dim count As Integer
count = 0

For Each cell In rng
    If cell.Value = key.Value Then
        count = count + 1
    End If
Next cell

countif_n = count

End Function

Here rng is your non-contigous range, and key represent the "range"(cell) which contains the value you want to count. For eg., to check for 1 enter 1 in any cell lets suppose "F2", and your non-contigous range is "testrange"

Then use the above function by entering the following in any blank cell:

=countif_n(testrange, F2)

answered Jun 16, 2012 at 16:53

Kartik Anand

4,6395 gold badges47 silver badges75 bronze badges

2 Comments

whytheq Over a year ago

surely this is over complicating the situation

Daan Over a year ago

Thanks, I guess VBA is the answer then! :)

barry houdini · Accepted Answer · 2012-06-17 14:25:36Z

1

Although COUNTIF can't handle non-contiguous ranges some functions can, for example RANK and COUNT so for a range called Range this formula will give the number of instances of a number in Z2 within Range

=IFERROR(COUNT(Range)-SUM(RANK(Z2,Range,{1,0}))+2,0)

assumes Excel 2007 or later but can be amended to work in earlier versions

edited Jun 17, 2012 at 14:25

answered Jun 17, 2012 at 13:42

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Comments

Doug Glancy · Accepted Answer · 2012-06-16 18:41:26Z

0

This doesn't quite work if there's stuff below S7 that can't be counted, but you may be able to modify. It also doesn't incorporate the named range.

=SUM(IF(MOD(COLUMN(A2:S31),2)=0,IF(A2:S31=2,1,0)))

This example counts the number of 2's.

This needs to be array-entered with ctrl-shift-enter. It's based on the fact that you're counting in every other column, at least in your example. Also, although you mention the columns are different heights, it looks like all except S are the same height. So maybe there's a way to work around that.

edited Jun 16, 2012 at 18:41

answered Jun 16, 2012 at 18:35

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Collectives™ on Stack Overflow

Excel: Count same number values in noncontiguous range

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related