VLOOKUP

Posted by Tom on May 26, 2000 12:29 PM

I've tried various combinations of VLOOKUP, INDEX, and COUNTIF without luck. I'm trying to count the number of times a set of conditions repeats itself in an array. For example:

Col A Col B
1 Tech 05/01/00
2 Soft 05/01/00
3 Admin 05/10/00
4 Tech 05/01/00
5 Soft 05/20/00

What formula can be used to count the number of times Col A = Tech and Col B = 05/01/00.

Posted by Celia on May 26, 2000 1:25 PM

Tom
Heres one way. The formulas are array formulas. They must be entered with Ctrl+Shift+Enter

If column B is formatted as Date :-

=SUM((A1:A5="Tech")*(B1:B5=36647))

36647 is the serial number value of 05/01/00 (assuming mm/dd/yy).

If column B is formatted as Text :-

=SUM((A1:A5="Tech")*(B1:B5=05/01/00))

Theres some good info re array formulas at :-

http://www.cpearson.com/excel/array.htm

Celia