Need know what are valid SUMPRODUCTS arguments and functions
 

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'!D2:D65536="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!

Need know what are valid SUMPRODUCTS arguments and functions
 

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

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'!D2:D65536="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!

Need know what are valid SUMPRODUCTS arguments and functions
 

Either:

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

Or:

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

sparham wrote:
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'!D2:D65536="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!