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!

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!

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!