Excel 2003 function for counting rows with two conditions.

[Guest]

Hi, please help !.
I have a worksheet with many rows.
In each row I have column 10 with a text value and column 11 with a text
value.
In each row I want a column to tell me how many rows there are with the same
values in columns 10 and 11 as the current row.
Which function could I use to do this ?.
I can use the COUNTIF function to count the number of rows based on the
contents of a cell in one column but can't figure out how to base the result
on two columns.
This is Excel 2003 running on windows vista.
Thanks in advance for your help.

 

[T. Valko]

If I understood you, try something this:

=SUMPRODUCT(--(J$2:J$20=J2),--(K$2:K$20=K2))

Copy down as needed.

Biff

 

[Stan Brown]

Sun, 24 Jun 2007 13:08:03 -0700 from NikkiTheNovice

In each row I want a column to tell me how many rows there are with the same
values in columns 10 and 11 as the current row.
Which function could I use to do this ?.
I can use the COUNTIF function to count the number of rows based on the
contents of a cell in one column but can't figure out how to base the result
on two columns.

I think you want to use SUMPRODUCT for this.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new

 

[Guest]

Concatenate columns 10 (J) and 11 (K) in a new column (hide it if necessary)
and countif concatenated value to this new column
Insert column L with L2=J2&K2
copy down as needed (example upto L500)

Your COUNTIF in a cell of row 2 has to be
=COUNTIF($L$2:$L$500;J2&K2)
Copy again down as needed.

T.

PS. Use "," instead of ";" depending on your regional settings