Excel 2003 function for counting rows with two conditions.
 

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.

Excel 2003 function for counting rows with two conditions.
 

If I understood you, try something this:

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

Copy down as needed.

Biff

"NikkiTheNovice" wrote in message
...
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.

Excel 2003 function for counting rows with two conditions.
 

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

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Excel 2003 function for counting rows with two conditions.
 

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

"NikkiTheNovice" wrote:

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.