Countif help
Posted by Gee-off on January 03, 2002 11:53 AM
Here's a sample spreadsheet:
Year|Type
1991|1
1991|2
1991|1
1992|1
1992|1
1994|2
1995|2
My question is, how can I count how many 1991's are a type 1?
I don't want to sort it to figure it out.
I can use countif to count how many type 1's and how many 1991's. But not how many are both.
Please help.
Posted by IML on January 03, 2002 12:01 PM
You could use
=SUMPRODUCT((A1:A10=1991)*(B1:B10=1))
or array enter
=SUM((A1:A10=1991)*(B1:B10=1))
assuming your range is A1:b10
good luck
Posted by Aladin Akyurek on January 03, 2002 12:03 PM
It's:
=SUMPRODUCT((A2:A100=1999)*(B2:B100=1))
where A2:A100 houses year entered as 1999, 2000, etc and B2:B100 the types.
Aladin
========
Posted by Gee-off on January 03, 2002 12:06 PM
[b]: You could use
Nope! I already know that won't work. The correct answer here should be 2. That's how many type 1's there were in 1991.
I think your solution would give me (1991*1991)+(1*1).
Posted by Aladin Akyurek on January 03, 2002 12:18 PM
Gee-off: Try before you conclude... [NT]
[b]: You could use
Posted by Gee-off on January 03, 2002 12:31 PM
Of course! a=1991 is a boolean value! DUH!!
Yielding (1*1) + (1*1) + (0*0)... = the total count.
I shoulda thought b4 criticizing.
Bahh! I could figure this problem out easy in any programming language. Oh well...thanks guys!
Posted by Aladin Akyurek on January 03, 2002 12:34 PM
No problem... You're welcome. [NT]
Of course! a=1991 is a boolean value! DUH!!
