counting functions

[Jared]

I am using 97 and have a table that consists of 2
columns. each cell has either a 3 or a 0 in it. I want
to count the number of rows that have a 3 in both, 3 in
the first and a 0 in the second, and a 0 in the first and
a e in the second. Thanks for the help.

 

[Frank Kabel]

Hi
try something like
=SUMPRODUCT(--(A1:A100=3),--(B1:B100=3))

for younting the rows that have a '3' in both column

 

[Frank Kabel]

Hi
problem with this COUNTIF formula is that a row which contains two
times the value '3' would be counted as '2' and not as only ONE single
row.

So in this case probably using SUMPRODUCT to create a conditional count
with two conditions seems to be the way to go

P.S.: both COUNTIF / SUMPRODUCT are available in Excel 97

 

[Jared]

I think whqt i wrote was mistaken. I want to count the
numer of rows that have either a 3 in both columns or only
1. For example:
3 3
0 3
3 3
0 3
3 0

I need a function that will tell me that there are 2 rows
with a 3 in both, 2 rows with a 3 in the second column
only, and 1 row with a 3 in the first column only.

 

[Domenic]

Hi Jared,

Try,

=SUMPRODUCT((A1:A10=3)*(B1:B10=3))

=SUMPRODUCT((A1:A10<>3)*(B1:B10=3))

=SUMPRODUCT((A1:A10=3)*(B1:B10<>3))

Hope this helps!