• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 014 - Count number of times a numeric array occurs in another


Excel Ninja
Challenge Name

-- Count the number of times a numeric 2-d array occurs in another 2-d array

Challenge Description

-- This is a formula challenge. As such, only formula submissions are accepted.

-- For two lists named "nList" and "sList", count the number of times "sList" appears in "nList".

-- To keep things simple, we will assume that both nList and sList have two columns each

-- Also, to simplify things, we will assume that sList has exactly two rows

-- Also, to simplify things, assume that sList will have at least one non-zero value

-- sList and nList may be located anywhere on a worksheet

-- nList has more rows than sList

-- nList and sList contain values that are not in any sequence

-- Goal is to calculate the count without using any helper cells.

We will remove some of these assumptions in a future challenge that tackles a more general case.

Sample data and output:


1	2
3	4
5	6
7	8
3	4
7	6
3	4
5	6
1	0
2	3

3	4
5	6
In this case, the output expected is 2
since sList occurs twice
in nList.



Luke M

Excel Ninja
Somehow, I have a feeling this requires the use of the mystical MMULT function, or which I have little understanding. My attempt to summon the dark arts...

=MMULT(MMULT(1,nList,sList)+INDIRECT("Eye of newt"),TEXT(101,-PI()^SQRT(-1)))

Rats, no good...looking forward to seeing what some others come up with.

Happy Friday everyone!


Excel Ninja
Hi Luke,

I think a mixture of the following will also help!

Eye of newt, and toe of frog,

Wool of bat, and tongue of dog,

Adder's fork, and blind-worm's sting,

Lizard's leg, and howlet's wing

But really, I think this is easier than what you are picturing! :)


Sam Mathai Chacko

Active Member
Here's a hard coded bazooka



Excel Ninja
Hi Sajan ,

Similar to Sam's , hard-coded :




Active Member
Without any peeking whatsoever, here's mine:


Or even shorter using INDEX:



Active Member
I should point out that my above formula only handles data that is 2 columns wide.

Here's a formula that handles any number of columns (although it still only handles a lookup list that is 2 rows deep)


Edit: Whoops, I think I've preempted Sajan's 2nd iteration of this challenge. Sorry 'bout that.

Anyway, I better get started on his likely 3rd iteration, where both data sets share the same about of columns but the column width is > 2, AND sList can be any number of rows. (Don't worry, won't post it here until a challenge is officially called).

Good challenge Sajan

Matt Louis

New Member

This works for me, yielding the same array and result as previous respondents' formulas. Assumes nList is A2:B11 and sList is D2:E3.

However, its relative simplicity makes me think I'm overlooking something...


Active Member
Matt, this misses the requirement that sList and nList may be located anywhere on a worksheet. i.e. we're not allowed to hard-code the address where the lists are into the formula