Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting on two conditions, one with a range | Excel Worksheet Functions | |||
Hiding rows that meet multiple conditions in excel | Excel Discussion (Misc queries) | |||
How Do I have excel count 2 cells with conditions (Excel 2003) | New Users to Excel | |||
Looking for experience with counting and conditions in excel 2003 | Excel Worksheet Functions | |||
counting based on 2 conditions | Excel Discussion (Misc queries) |