Workaround no COUNTIFS in 2003

F

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
 
P

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
 
G

Gord Dibben

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

Note the removal of the double quotes from "1"


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top