Creating Nested Functions
 

I really need some help to write a formula with nested functions.

How do I write a formula to calculate how many times a specific combination
of values appear in two different columns.

i.e. column A is populated with the values 1,2 and 3 in random order;
column B is populated with values a,b and c, also in random order. What
would the formula be to determine the number of times that the combination of
1 and a appear side by side on the same row; over a worksheet with 10 rows.

Thanks for any help in advance.

Creating Nested Functions
 

Try this:

=SUMPRODUCT(--(A1:A10=1),--(B1:B10="A"))

Better to use cells to hold the criteria:

C1 = 1
C2 = A

=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2))

Biff

"Texicanslim" wrote in message
...
I really need some help to write a formula with nested functions.

How do I write a formula to calculate how many times a specific
combination
of values appear in two different columns.

i.e. column A is populated with the values 1,2 and 3 in random order;
column B is populated with values a,b and c, also in random order. What
would the formula be to determine the number of times that the combination
of
1 and a appear side by side on the same row; over a worksheet with 10
rows.

Thanks for any help in advance.

Creating Nested Functions
 

You need an array formula that takes performs a countif using multiple
criteria. Take a look at Chip Pearson's website. Specifically...
http://www.cpearson.com/excel/array.htm
--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Texicanslim" wrote:

I really need some help to write a formula with nested functions.

How do I write a formula to calculate how many times a specific combination
of values appear in two different columns.

i.e. column A is populated with the values 1,2 and 3 in random order;
column B is populated with values a,b and c, also in random order. What
would the formula be to determine the number of times that the combination of
1 and a appear side by side on the same row; over a worksheet with 10 rows.

Thanks for any help in advance.

Creating Nested Functions
 

This seems to be headed in the right direction; the cell in which the
calculated amount is to be placed shows #value rather than actual number of
occurances.

Tex

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A10=1),--(B1:B10="A"))

Better to use cells to hold the criteria:

C1 = 1
C2 = A

=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2))

Biff

"Texicanslim" wrote in message
...
I really need some help to write a formula with nested functions.

How do I write a formula to calculate how many times a specific
combination
of values appear in two different columns.

i.e. column A is populated with the values 1,2 and 3 in random order;
column B is populated with values a,b and c, also in random order. What
would the formula be to determine the number of times that the combination
of
1 and a appear side by side on the same row; over a worksheet with 10
rows.

Thanks for any help in advance.

Creating Nested Functions
 

Post the *EXACT* formula you tried.

The ranges have to be the same size and you can't use entire columns (A:A,
B:B) as range references (unless you're using Excel 2007).

Biff

"Texicanslim" wrote in message
...
This seems to be headed in the right direction; the cell in which the
calculated amount is to be placed shows #value rather than actual number
of
occurances.

Tex

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A10=1),--(B1:B10="A"))

Better to use cells to hold the criteria:

C1 = 1
C2 = A

=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2))

Biff

"Texicanslim" wrote in message
...
I really need some help to write a formula with nested functions.

How do I write a formula to calculate how many times a specific
combination
of values appear in two different columns.

i.e. column A is populated with the values 1,2 and 3 in random order;
column B is populated with values a,b and c, also in random order.
What
would the formula be to determine the number of times that the
combination
of
1 and a appear side by side on the same row; over a worksheet with 10
rows.

Thanks for any help in advance.

Creating Nested Functions
 

T. Valko wrote...
....
Better to use cells to hold the criteria:

C1 = 1
C2 = A

=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2))
....

Could take advantage of the adjacency of the data ranges and enter the
criteria in C1:D1, then try

=SUMPRODUCT(--(A1:B10=C1:D1))

Creating Nested Functions
 

That's the same as:

=COUNTIF(A1:A10,C1)+COUNTIF(B1:B10,D1)

The OP wanted the count where both criteria are on the same row.

Biff

"Harlan Grove" wrote in message
oups.com...
T. Valko wrote...
...
Better to use cells to hold the criteria:

C1 = 1
C2 = A

=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2))
...

Could take advantage of the adjacency of the data ranges and enter the
criteria in C1:D1, then try

=SUMPRODUCT(--(A1:B10=C1:D1))

Creating Nested Functions
 

T. Valko wrote...
That's the same as:

=COUNTIF(A1:A10,C1)+COUNTIF(B1:B10,D1)

The OP wanted the count where both criteria are on the same row.
....

You're right. It'd need to be something like

=COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2))

but nothing gained with just two sets of comparisons.

Creating Nested Functions
 

Let me pick your brain......

=COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2))
=SUMPRODUCT(--(MMULT(--(A1:B10=C1:D1),{1;1})=2))

Other than one being a few keystrokes longer, which is better?

Also, how do you get around the MMULT array size limit? I don't have a
specific example in mind, just in general terms.

Biff

"Harlan Grove" wrote in message
ups.com...
T. Valko wrote...
That's the same as:

=COUNTIF(A1:A10,C1)+COUNTIF(B1:B10,D1)

The OP wanted the count where both criteria are on the same row.
...

You're right. It'd need to be something like

=COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2))

but nothing gained with just two sets of comparisons.

Creating Nested Functions
 

T. Valko wrote...
Let me pick your brain......

=COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2))
=SUMPRODUCT(--(MMULT(--(A1:B10=C1:D1),{1;1})=2))

Other than one being a few keystrokes longer, which is better?

For just two vector comparisons, neither is particularly good compared
to your formula (modified)

=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=D1))

Things change when there are more.

=COUNT(1/(MMULT(--(A3:G12=A1:G1),{1;1;1;1;1;1;1})=7))

vs

=SUMPRODUCT(--(A3:A12=A1),--(B3:B12=B1),--(C3:C12=C1),--(D3:D12=D1),--(E3:E12=E1),--(F3:F12=F1),
--(G3:G12=G1))

Also, how do you get around the MMULT array size limit? I don't have a
specific example in mind, just in general terms.
....

Not possible, but Excel 2003 seems to be able to handle a fair number
of vectors with 5461 rows. Fill A1:J5500 with =ROW(). Enter the
following.

M1:
10

M2:
5461

N1:
=SUMPRODUCT(MMULT(A1:INDEX($1:$65536,M2,M1),ROW(A1 :INDEX(A:A,M1))^0))/M1

N2:
=(M2+1)*M2/2

N1 and N2 return the same result, the sum of all integers from 1 to
5461.

Creating Nested Functions
 

=COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2))
=SUMPRODUCT(--(MMULT(--(A1:B10=C1:D1),{1;1})=2))
Other than one being a few keystrokes longer, which is better?

For just two vector comparisons, neither is particularly good compared
to your formula (modified)

For just 2 or 3 comparisons I wouldn't use MMULT.

What I meant was, which is better as far as:

=COUNT(1/(MMULT
=SUMPRODUCT(--(MMULT

It seems to me that Sumproduct has a slight advantage.

Biff

"Harlan Grove" wrote in message
oups.com...
T. Valko wrote...
Let me pick your brain......

=COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2))
=SUMPRODUCT(--(MMULT(--(A1:B10=C1:D1),{1;1})=2))

Other than one being a few keystrokes longer, which is better?

For just two vector comparisons, neither is particularly good compared
to your formula (modified)

=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=D1))

Things change when there are more.

=COUNT(1/(MMULT(--(A3:G12=A1:G1),{1;1;1;1;1;1;1})=7))

vs

=SUMPRODUCT(--(A3:A12=A1),--(B3:B12=B1),--(C3:C12=C1),--(D3:D12=D1),--(E3:E12=E1),--(F3:F12=F1),
--(G3:G12=G1))

Also, how do you get around the MMULT array size limit? I don't have a
specific example in mind, just in general terms.
...

Not possible, but Excel 2003 seems to be able to handle a fair number
of vectors with 5461 rows. Fill A1:J5500 with =ROW(). Enter the
following.

M1:
10

M2:
5461

N1:
=SUMPRODUCT(MMULT(A1:INDEX($1:$65536,M2,M1),ROW(A1 :INDEX(A:A,M1))^0))/M1

N2:
=(M2+1)*M2/2

N1 and N2 return the same result, the sum of all integers from 1 to
5461.

Creating Nested Functions
 

T. Valko wrote...
....
For just 2 or 3 comparisons I wouldn't use MMULT.

What I meant was, which is better as far as:

=COUNT(1/(MMULT
=SUMPRODUCT(--(MMULT

It seems to me that Sumproduct has a slight advantage.
....

Actually, I'd guess COUNT would be quicker because it's only checking
each entry's data type while SUMPRODUCT is summing them, and I suspect
summing always takes place in double precision.

Creating Nested Functions
 

I think you're right. Thanks, Harlan.

Biff

"Harlan Grove" wrote in message
ups.com...
T. Valko wrote...
...
For just 2 or 3 comparisons I wouldn't use MMULT.

What I meant was, which is better as far as:

=COUNT(1/(MMULT
=SUMPRODUCT(--(MMULT

It seems to me that Sumproduct has a slight advantage.
...

Actually, I'd guess COUNT would be quicker because it's only checking
each entry's data type while SUMPRODUCT is summing them, and I suspect
summing always takes place in double precision.