Counting a column based on the results of 2 other columns

[Guest]

I am trying to count the number of occurences of something in one column depending on a couple of other columns.

e.g.

Available Proof of ID
Y 1
Y x
N 0
N/A x

In this case I would want to total the number of entries in the Proof of ID column (both the 1's and 0's, but not the x's) where there is not an N/A in the availability column, so it would show me 2.

Anyone with any ideas??

 

[Max]

Assuming your table is in cols A and B, data from row2 down

Try in say, C2, something like:

=SUMPRODUCT((A2:A100<>"N/A")*(A2:A100<>"")*(B2:B100<>"x"))

The above will return the count of "2" for the sample in your post

Note that the ranges have to be similar (viz. A2:A100, B2:B100)
and that entire column references (e.g.: A:A, B:B) cannot be used
in SUMPRODUCT

Adjust the ranges to suit
(A100 and B100 as the last row references are arbitrary)

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

I am trying to count the number of occurences of something in one column

depending on a couple of other columns.

e.g.

Available Proof of ID
Y 1
Y x
N 0
N/A x

In this case I would want to total the number of entries in the Proof of

ID column (both the 1's and 0's, but not the x's) where there is not an N/A
in the availability column, so it would show me 2.