SUM and Wildcard search

[AdamA]

Hello guys,

I'm trying to figure out why this formula isn't working, and hope you can help me. I have a search on a sheet to tell me the number of times two criteria are met in a row, this works fine for exact matches:

{=SUM((Data!AM:AM="iPA Workbench error")*(Data!K:K="IT/Software - Europe/E-L/iPA/Workflow / AEP"))}

However, I want to be able to search the first section as a wildcard, and I've tried the following without any luck:

{=SUM((Data!AM:AM="*Workbench*")*(Data!K:K="IT/Software - Europe/E-L/iPA/Workflow / AEP"))}

Is there something I am doing wrong here? I've tried the seach on the forums but couldn't see anything that would help with where I'm going wrong.

Many thanks.

 

[Jon von der Heyden]

Hello Adam

Try;
=SUMPRODUCT(--(ISNUMBER(SEARCH("workbench",AM1:AM1000))),--(K1:K1000="IT/Software - Europe/E-L/iPA/Workflow / AEP"))

Note that unless using XL07 (or later) you cannot use whole columns, so you must specify row #'s. Although I assume you are using XL07 or later as you used whole columns in your previous formula.

 

[AdamA]

That works a treat, thank you kindly.

Adam.

 

[AdamA]

Just one more thing.

Is it possible to throw a date search in here too?

I have a 'log date' column, with dates from 01/07/2009 to 28/02/2010 and want to be able to show criteria from July for example. I would have guessed I need to add something to the end of this to show range 01/07/2009-31/07/2009 for example, or is there an easier way to do this?

=SUMPRODUCT(--(ISNUMBER(SEARCH("LVR9",Data!AT:AT))),--(Data!K:K="IT/Software - Europe/E-L/iPA/Workflow / AEP"))

Thanks :D

 

[Jon von der Heyden]

Something like:

=SUMPRODUCT(--(ISNUMBER(SEARCH("LVR9",Data!AT:AT))),--(Data!K:K="IT/Software - Europe/E-L/iPA/Workflow / AEP"),--(TEXT(A:A,"mm-yy")="07-10"))

Where dates are in column A...