Comparing and potentially adding two fields
 

In Excel 2003 I have two field ranges (E6:E1000) and (T6:T1000)

The first column will either be blank, "Open" or "Close"
The second column will either be blank or a number (0,1,2,3,..)

I have been trying to figure out a statement (countif does not help)
that looks at the two field ranges (respectively line for line) and if
the first field is "Close" and the second field is "0" then it adds a
1.

For example:

Row......E........T
06......Open......3
07......Close.....0
08......Open......0
09......Open......1
10......Close.....3
11......Close.....0

The formula (in the above info) should return a count of 2, because
there are two rows that have the statements "CLOSE" AND "0"

Thanks in advance,

Avi

one way:

=SUMPRODUCT(--(E6:E1000="Close"),--(T6:T1000=0))

In article ,
(Avi) wrote:

In Excel 2003 I have two field ranges (E6:E1000) and (T6:T1000)

The first column will either be blank, "Open" or "Close"
The second column will either be blank or a number (0,1,2,3,..)

I have been trying to figure out a statement (countif does not help)
that looks at the two field ranges (respectively line for line) and if
the first field is "Close" and the second field is "0" then it adds a
1.

For example:

Row......E........T
06......Open......3
07......Close.....0
08......Open......0
09......Open......1
10......Close.....3
11......Close.....0

The formula (in the above info) should return a count of 2, because
there are two rows that have the statements "CLOSE" AND "0"

Thanks in advance,

Avi