MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!!