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
