Need a wild card in a formula for counting incidents with multiple criteria

Typing a store number in K3 will bring up the numbers for the store perfectly. I need to add a wild card so it will bring up all store numbers when asked. I have tried Cover!$K$3&”*” but that didn’t work.

Hopefully this will be the last change that needs to be made for this workbook.

I have attached what I am working on.

You can only use a wildcard if you have text values. Try it like this in E7 (changes shown in red):

=SUMIFS(INDIRECT(E$5&"!F:F"),INDIRECT(E$5&"!A:A"),IF($K$3="",">0",$K$3),INDIRECT(E$5&"!E:E"),">="&IF(MONTH($D7)=1,DATE(E$5+1,1,1),DATE(E$5,MONTH($D7),1)),INDIRECT(E$5&"!E:E"),"<="&IF(MONTH($D7)=1,EOMONTH(DATE(E$5+1,1,1),0),EOMONTH(DATE(E$5,MONTH($D7),1),0)),INDIRECT(E$5&"!C:C"),"ASSOCIATE THEFT")

and similar for the other cells on that row, then copy down. Now if K3 is empty, then all stores will be included, but you could change it to IF($K$3="All" ... , so you would have to put the word All in K3.

Hope this helps.

Pete

Originally Posted by Pete_UK

You can only use a wildcard if you have text values. Try it like this in E7 (changes shown in red):

=SUMIFS(INDIRECT(E$5&"!F:F"),INDIRECT(E$5&"!A:A"),IF($K$3="",">0",$K$3),INDIRECT(E$5&"!E:E"),">="&IF(MONTH($D7)=1,DATE(E$5+1,1,1),DATE(E$5,MONTH($D7),1)),INDIRECT(E$5&"!E:E"),"<="&IF(MONTH($D7)=1,EOMONTH(DATE(E$5+1,1,1),0),EOMONTH(DATE(E$5,MONTH($D7),1),0)),INDIRECT(E$5&"!C:C"),"ASSOCIATE THEFT")

and similar for the other cells on that row, then copy down. Now if K3 is empty, then all stores will be included, but you could change it to IF($K$3="All" ... , so you would have to put the word All in K3.

Hope this helps.

Pete

Thank you Pete, I went with the asterisk "*".