# How to count events based on several criteria + wildcards

#### glorber

##### New Member
Hi,

I have a sheet similar to the following:
ESWL_1.xls
ABCD
1DateOccKey
21/1/19851ab
31/1/19861ac
41/1/19871b
51/1/19882d
61/1/19891e
71/1/19904a
81/1/19911dc
91/1/19922b
101/1/19931ba
111/1/19943c
121/1/19951d
131/1/19961e
141/1/19971f
151/1/19985fd
161/1/19991dc
171/1/20006af
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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### xenou

##### MrExcel MVP
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

##### MrExcel MVP
Control+shift+enter...

Code:
``````=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.

Replies
2
Views
259
Replies
2
Views
251
Replies
5
Views
424
Replies
3
Views
287
Replies
2
Views
149

1,191,287
Messages
5,985,762
Members
439,981
Latest member
ofori francis

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back