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.

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.

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.

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.

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))

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))

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.

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.

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.