Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - COUNTIF
I am using Excel 2000 and Windows XP. I have 2 worksheets. The first
worksheet has the formula I am trying to create. The second worksheet has 2 columns, the first column has the values A5/4, A6/4 etc. the second column has various values eg OP, BS etc I am trying to use COUNTIF in the first worksheet to count the number of times A5/4 AND OP occurs in the same row. Many thanks in advance. -- Richard |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - COUNTIF
The easiest thing I can think of is to create a helper column which joins the
values in each column and then counts off that combined column. Example: assume the first column of the spreadsheet is A and the second column is B. =CONCATENATE(A1,B1) would join the values in the first row. Fill down as necessary. Assuming these concatenations are done in column C, then doing =COUNTIF(C1:C100="A5/4OP") will give you the count of rows in which both A5/4 and OP occur. Dave -- Brevity is the soul of wit. "Richard" wrote: I am using Excel 2000 and Windows XP. I have 2 worksheets. The first worksheet has the formula I am trying to create. The second worksheet has 2 columns, the first column has the values A5/4, A6/4 etc. the second column has various values eg OP, BS etc I am trying to use COUNTIF in the first worksheet to count the number of times A5/4 AND OP occurs in the same row. Many thanks in advance. -- Richard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - COUNTIF
For more than one criteria use SUMPRODUCT
=SUMPRODUCT(--(Sheet2!A1:A100="A5/2"),--(Sheet2!B1:B100="OP")) for details see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html An alternative for you is Pivot Table best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Richard" wrote in message ... I am using Excel 2000 and Windows XP. I have 2 worksheets. The first worksheet has the formula I am trying to create. The second worksheet has 2 columns, the first column has the values A5/4, A6/4 etc. the second column has various values eg OP, BS etc I am trying to use COUNTIF in the first worksheet to count the number of times A5/4 AND OP occurs in the same row. Many thanks in advance. -- Richard |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - COUNTIF
Actually ignore my suggestion. Bernard's sounds more efficient.
Dave -- Brevity is the soul of wit. "Dave F" wrote: The easiest thing I can think of is to create a helper column which joins the values in each column and then counts off that combined column. Example: assume the first column of the spreadsheet is A and the second column is B. =CONCATENATE(A1,B1) would join the values in the first row. Fill down as necessary. Assuming these concatenations are done in column C, then doing =COUNTIF(C1:C100="A5/4OP") will give you the count of rows in which both A5/4 and OP occur. Dave -- Brevity is the soul of wit. "Richard" wrote: I am using Excel 2000 and Windows XP. I have 2 worksheets. The first worksheet has the formula I am trying to create. The second worksheet has 2 columns, the first column has the values A5/4, A6/4 etc. the second column has various values eg OP, BS etc I am trying to use COUNTIF in the first worksheet to count the number of times A5/4 AND OP occurs in the same row. Many thanks in advance. -- Richard |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - COUNTIF
Thank you anyway Dave, it is most appreciated.
-- Richard "Dave F" wrote: Actually ignore my suggestion. Bernard's sounds more efficient. Dave -- Brevity is the soul of wit. "Dave F" wrote: The easiest thing I can think of is to create a helper column which joins the values in each column and then counts off that combined column. Example: assume the first column of the spreadsheet is A and the second column is B. =CONCATENATE(A1,B1) would join the values in the first row. Fill down as necessary. Assuming these concatenations are done in column C, then doing =COUNTIF(C1:C100="A5/4OP") will give you the count of rows in which both A5/4 and OP occur. Dave -- Brevity is the soul of wit. "Richard" wrote: I am using Excel 2000 and Windows XP. I have 2 worksheets. The first worksheet has the formula I am trying to create. The second worksheet has 2 columns, the first column has the values A5/4, A6/4 etc. the second column has various values eg OP, BS etc I am trying to use COUNTIF in the first worksheet to count the number of times A5/4 AND OP occurs in the same row. Many thanks in advance. -- Richard |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - COUNTIF
Thank you Bernard, that has solved my problem.
-- Richard "Bernard Liengme" wrote: For more than one criteria use SUMPRODUCT =SUMPRODUCT(--(Sheet2!A1:A100="A5/2"),--(Sheet2!B1:B100="OP")) for details see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html An alternative for you is Pivot Table best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Richard" wrote in message ... I am using Excel 2000 and Windows XP. I have 2 worksheets. The first worksheet has the formula I am trying to create. The second worksheet has 2 columns, the first column has the values A5/4, A6/4 etc. the second column has various values eg OP, BS etc I am trying to use COUNTIF in the first worksheet to count the number of times A5/4 AND OP occurs in the same row. Many thanks in advance. -- Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Eliminate anti-rollback in Excel 2000 | Excel Discussion (Misc queries) | |||
View / Edit Excel 2003 files in Excel 2000 | Excel Discussion (Misc queries) | |||
Activate method of Worksheet class fails in Excel 2000 | Excel Discussion (Misc queries) | |||
Excel 2000 file when opened in Excel 2003 generates errors? | Excel Discussion (Misc queries) |