| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countifs in 2003 = _xlfn. ?? | Excel Worksheet Functions | |||
| Not understanding COUNTIFS workaround in 2003 | Excel Discussion (Misc queries) | |||
| Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
| COUNTIFS for 2003 | Excel Worksheet Functions | |||
| excel's new countifs...on 2003? | Excel Discussion (Misc queries) | |||
Linear Mode
