Excel 2003 function for counting rows with two conditions.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
If I understood you, try something this:

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

Copy down as needed.

Biff
 
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.
Click to expand...

I think you want to use SUMPRODUCT for this.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new
 
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
 
Back
Top