SumProduct Difference Between Date Range & "*" Usage

Hi,


I have 2 identical sumproduct fuctions as follows:

=SUMPRODUCT(--($G$4:$G$1215>=G1222),--($G$4:$G$1215<=G1223),--($L$4:$L$1215="*XYZ*"))

Results with incorrect value = 0

=SUMPRODUCT(--($G$4:$G$1215>=G1222),--($G$4:$G$1215<=G1223),--($L$4:$L$1215=G1220))

Results with correct value = 3

Both G1220 or "*XYZ*" represents the same reference but why is it profiding different results?

"XYZ" represents a company & I'm want to count the total instances that "XYZ" has been assigned a job during a specified duration, in this instance, during the month of October.

Right now, the data is being extracted from a data log that I can't touch, which is part of the reason I would like to use the "*XYZ*" (wildcard) with the intent to make sure it captures the company, in case someone does not enter properly or adds extra space.

Now, I'm trying to figure out why I'm getting different results for identical functions that should that should provide the same results.

Once, I have this function, I would like to copy it into a new file

How can I use the same intent & be a 100% confident that the function in the new file is extractring properly from my datalog file? Feel free to make up new file.

I would attach a file but for some reason, I'm not able too.


Thanks

Try
=sumproduct(--($g$4:$g$1215>=g1222),--($g$4:$g$1215<=g1223),--(isnumber(search("xyz",$l$4:$l$1215))))

I think that without your data without confidentional information it's hard to see, where the formula goes wrong.

Have you also looked at pivottable to solve your problem.

When you use a "comparison operator" like = wildcards aren't recognised so your * are treated as literal asterisks so $L$4:$L$1215="*XYZ*" will only be TRUE when one of those cells literally equals "*XYZ*"

Martin's solution should work for you, although if you have excel 2007 or later you can use COUNTIFS which will recognise wildcards, i.e.

=COUNTIFS($G$4:$G$1215,">="&G1222,$G$4:$G$1215,"<="&G1223,$L$4:$L$1215,"*XYZ*")

Hi Everyone,

Thanks for the feedback - I will be trying these suggestions soon & will let you know how they work for me.

Hi oeldere & daddylonglegs,

Both of these functions work great! Thanks for these. Now, if I wanted to add a 4th criteria to search one more column & find, let's say MNO in column cells,$b$4:$b$1215, how could I modify or add to one of your examples?

Thanks again for the assistance...

not me then!

Hi martindwilson,

Sorry - I forgot to mention your name too - Yes - Your function works great too!

Hi oeldere, martindwilson & daddylonglegs,

All of your functions or suggestions work great with my test sample file. However, I have yet to test them on my primary file, but too busy to test. I'll let you know how this goes shortly.

Thanks again everyone for your assistance.