Workaround no COUNTIFS in 2003
 

Hi,

Can anyone help me with this?

I've read all the threads regarding alternatives to not having COUNTIFS in
2003, and none of the solutions seem to work.

I'm trying to find out how many rows have both 1 (Column A) and A1 (Column
B). The answer should be 4.

My actual set is much larger (669 rows) which may or maynot be the problem.
When I tried it out on a row of 15, the sumproduct function seems to work,
but when I try it on the full set, these are the errors I get back.

I've tried
=SUMPRODUCT((A2:A669="1")*(B2:B669="A1"))
I get 'O' when the answer is obviously not 0

=SUMPRODUCT(--(A:A= "1"),--(B:B ="A1"))
(I selected the entire columns) and I get the #NUM1 error

Column A Column B
1 A1
1 A1
1 A1
1 A2
1 A3
1 A2
1 A1
1 A2

Workaround no COUNTIFS in 2003
 

You can't use full-column references with SUMPRODUCT in XL2003 or
earlier.

Also, I suspect you have the numeric value 1 in column A and not the
text value "1", so change your formula to this:

=SUMPRODUCT((A2:A669=1)*(B2:B669="A1"))

Hope this helps.

Pete

On Nov 10, 12:02*am, fay wrote:
Hi,

Can anyone help me with this?

I've read all the threads regarding alternatives to not having COUNTIFS in
2003, and none of the solutions seem to work.

I'm trying to find out how many rows have both 1 (Column A) and A1 (Column
B). *The answer should be 4. *

My actual set is much larger (669 rows) which may or maynot be the problem. *
When I tried it out on a row of 15, the sumproduct function seems to work,
but when I try it on the full set, these are the errors I get back.

I've tried
=SUMPRODUCT((A2:A669="1")*(B2:B669="A1"))
I get 'O' when the answer is obviously not 0

=SUMPRODUCT(--(A:A= "1"),--(B:B ="A1"))
(I selected the entire columns) and I get the #NUM1 error

Column A *Column B
1 * * * * * * * * A1
1 * * * * * * * * A1
1 * * * * * * * * A1 *
1 * * * * * * * * A2
1 * * * * * * * * A3
1 * * * * * * * * A2
1 * * * * * * * * A1 *
1 * * * * * * * * A2

Workaround no COUNTIFS in 2003
 

=SUMPRODUCT(--(A:A= "1"),--(B:B ="A1"))

Remove double quotes around the 1


"fay" wrote:

Hi,

Can anyone help me with this?

I've read all the threads regarding alternatives to not having COUNTIFS in
2003, and none of the solutions seem to work.

I'm trying to find out how many rows have both 1 (Column A) and A1 (Column
B). The answer should be 4.

My actual set is much larger (669 rows) which may or maynot be the problem.
When I tried it out on a row of 15, the sumproduct function seems to work,
but when I try it on the full set, these are the errors I get back.

I've tried
=SUMPRODUCT((A2:A669="1")*(B2:B669="A1"))
I get 'O' when the answer is obviously not 0

=SUMPRODUCT(--(A:A= "1"),--(B:B ="A1"))
(I selected the entire columns) and I get the #NUM1 error

Column A Column B
1 A1
1 A1
1 A1
1 A2
1 A3
1 A2
1 A1
1 A2

Workaround no COUNTIFS in 2003
 

=SUMPRODUCT((A2:A669=1)*(B2:B669="A1"))

Note the removal of the double quotes from "1"


Gord Dibben MS Excel MVP

On Mon, 9 Nov 2009 16:02:01 -0800, fay
wrote:

Hi,

Can anyone help me with this?

I've read all the threads regarding alternatives to not having COUNTIFS in
2003, and none of the solutions seem to work.

I'm trying to find out how many rows have both 1 (Column A) and A1 (Column
B). The answer should be 4.

My actual set is much larger (669 rows) which may or maynot be the problem.
When I tried it out on a row of 15, the sumproduct function seems to work,
but when I try it on the full set, these are the errors I get back.

I've tried
=SUMPRODUCT((A2:A669="1")*(B2:B669="A1"))
I get 'O' when the answer is obviously not 0

=SUMPRODUCT(--(A:A= "1"),--(B:B ="A1"))
(I selected the entire columns) and I get the #NUM1 error

Column A Column B
1 A1
1 A1
1 A1
1 A2
1 A3
1 A2
1 A1
1 A2

Workaround no COUNTIFS in 2003
 

So simple! Thanks Teethless Mama!!

"Teethless mama" wrote:

=SUMPRODUCT(--(A:A= "1"),--(B:B ="A1"))

Remove double quotes around the 1


"fay" wrote:

Hi,

Can anyone help me with this?

I've read all the threads regarding alternatives to not having COUNTIFS in
2003, and none of the solutions seem to work.

I'm trying to find out how many rows have both 1 (Column A) and A1 (Column
B). The answer should be 4.

My actual set is much larger (669 rows) which may or maynot be the problem.
When I tried it out on a row of 15, the sumproduct function seems to work,
but when I try it on the full set, these are the errors I get back.

I've tried
=SUMPRODUCT((A2:A669="1")*(B2:B669="A1"))
I get 'O' when the answer is obviously not 0

=SUMPRODUCT(--(A:A= "1"),--(B:B ="A1"))
(I selected the entire columns) and I get the #NUM1 error

Column A Column B
1 A1
1 A1
1 A1
1 A2
1 A3
1 A2
1 A1
1 A2

Workaround no COUNTIFS in 2003
 

You're Welcome!


"fay" wrote:

So simple! Thanks Teethless Mama!!

"Teethless mama" wrote:

=SUMPRODUCT(--(A:A= "1"),--(B:B ="A1"))

Remove double quotes around the 1


"fay" wrote:

Hi,

Can anyone help me with this?

I've read all the threads regarding alternatives to not having COUNTIFS in
2003, and none of the solutions seem to work.

I'm trying to find out how many rows have both 1 (Column A) and A1 (Column
B). The answer should be 4.

My actual set is much larger (669 rows) which may or maynot be the problem.
When I tried it out on a row of 15, the sumproduct function seems to work,
but when I try it on the full set, these are the errors I get back.

I've tried
=SUMPRODUCT((A2:A669="1")*(B2:B669="A1"))
I get 'O' when the answer is obviously not 0

=SUMPRODUCT(--(A:A= "1"),--(B:B ="A1"))
(I selected the entire columns) and I get the #NUM1 error

Column A Column B
1 A1
1 A1
1 A1
1 A2
1 A3
1 A2
1 A1
1 A2