Need know what are valid SUMPRODUCTS arguments and functions

[Guest]

After running a query on a dbase, I'm trying to determine the number of items
it returned for given items. My formula is as follows:

=SUMPRODUCT(--('Refresh for Test Result 601'!D265536="CTI
Server"),--('Refresh for Test Result 601'!E2:E65536="Deferred"))

It has come to my attention that "CTI Server" has been entered as is and
also with trailing spaces. I need to know how to get the total for both.

I've tried "CTI Server%", "CTI Server*", "CTI Server~", "CTI Server?" and
"CTI Server~?" but it seems that SUMPRODUCT doesn't like this.

I also thought that perhaps I could change the operator to 'like' or 'in'
instead of '=' but that didn't work because it seems that SUMPRODUCT doesn't
like it either.

Any help provided for this would be greatly appreciated!

 

[Guest]

try
=SUMPRODUCT(--(trim('Refresh for Test Result 601'!D265536)="CTI
Server"),--('Refresh for Test Result 601'!E2:E65536="Deferred"))

 

[Aladin Akyurek]

Either:

=SUMPRODUCT(--(LEFT('Refresh for Test Result 601'!D265536,10)="CTI
Server"),--('Refresh for Test Result 601'!E2:E65536="Deferred"))

Or:

=SUMPRODUCT(--ISNUMBER(SEARCH("CTI Server",'Refresh for Test Result
601'!D265536,--('Refresh for Test Result 601'!E2:E65536="Deferred"))