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

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

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

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

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

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