How to count events based on several criteria + wildcards

[glorber]

Hi,

I have a sheet similar to the following:

ESWL_1.xls
	A	B	C	D
1	Date	Occ	Key
2	1/1/1985	1	ab
3	1/1/1986	1	ac
4	1/1/1987	1	b
5	1/1/1988	2	d
6	1/1/1989	1	e
7	1/1/1990	4	a
8	1/1/1991	1	dc
9	1/1/1992	2	b
10	1/1/1993	1	ba
11	1/1/1994	3	c
12	1/1/1995	1	d
13	1/1/1996	1	e
14	1/1/1997	1	f
15	1/1/1998	5	fd
16	1/1/1999	1	dc
17	1/1/2000	6	af
Sheet1

I would like to count all the events under the "Key" column containing the letter "a" (ie "*a*") taking place before date "1/1/1997". I tried nesting sum(if(range, criteria), but cannot make it accept the wildcard (*).

Thanks

 

[xenou]

In column D:
=IF(ISERROR(SEARCH("a",C3)),0,IF(A3>=DATEVALUE("1/1/1997"),1,0))

This searches for an a,
if it doesn't find one, then 0 is returned...
If it finds one, then a 1 is returned if the date is greater than 1/1/97

Fill this down and sum the column for your count.

Alternatively, you can do this with a single array formula:
In any single Cell:
=SUM(IF(ISERROR(SEARCH("a",$C$3:$C$16)),0,IF($A$3:$A$16>=DATEVALUE("1/1/1997"),1,0)))

Entered with Ctrl + Shift + Enter, not just enter. In my test the range I worked with was from row 3 to row 16, but you will need to adjust for your actual rows and columns.

Regards

 

[Aladin Akyurek]

Control+shift+enter...

=SUM(
    IF(ISNUMBER(SEARCH(E2,$C$2:$C$17)),
      IF(ISNUMBER($A$2:$A$17),
        IF($A$2:$A$17 < F2,$B$2:$B$17))))

E2 houses a substring condition like "a" and F2 a date condition like 1/1/1997.