Workaround no COUNTIFS in 2003

[fay]

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

 

[Pete_UK]

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

 

[Teethless mama]

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

Remove double quotes around the 1

 

[Gord Dibben]

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

Note the removal of the double quotes from "1"


Gord Dibben MS Excel MVP

 

[fay]

So simple! Thanks Teethless Mama!!

Remove double quotes around the 1

 

[Teethless mama]

You're Welcome!

So simple! Thanks Teethless Mama!!