How to use trim finction in array
 

Hi I have using array formula
=COUNT(IF(('sheet1'!$C$4:$C$200="corporate")*('she et1'!$L$4:$L$200="ip"),'sheet1'!$Q$4:$Q$200))
I want to trim all extra spaces in column c.
Please advise how to use trim function.
Thanks

How to use trim finction in array
 

=COUNT(IF((TRIM(Sheet1!$C$4:$C$200)="corporate")*( Sheet1!$L$4:$L$200="ip"),Sheet1!$Q$4:$Q$200))

"ub" wrote:

Hi I have using array formula
=COUNT(IF(('sheet1'!$C$4:$C$200="corporate")*('she et1'!$L$4:$L$200="ip"),'sheet1'!$Q$4:$Q$200))
I want to trim all extra spaces in column c.
Please advise how to use trim function.
Thanks

How to use trim finction in array
 

=SUMPRODUCT(ISNUMBER(SEARCH("corporate",'sheet1'!$ C$4:$C$200))*('sheet1'!$L$4:$L$200="ip"))


"ub" wrote:

Hi I have using array formula
=COUNT(IF(('sheet1'!$C$4:$C$200="corporate")*('she et1'!$L$4:$L$200="ip"),'sheet1'!$Q$4:$Q$200))
I want to trim all extra spaces in column c.
Please advise how to use trim function.
Thanks

How to use trim finction in array
 

Probably need to add a term to count only numbers in column Q:

=SUMPRODUCT(--ISNUMBER(SEARCH("corporate",Sheet1!$C$4:$C$200)),
--(Sheet1!$L$4:$L$200="ip"), --ISNUMBER(Sheet1!$Q$4:$Q$200))

In article ,
Teethless mama wrote:

=SUMPRODUCT(ISNUMBER(SEARCH("corporate",'sheet1'!$ C$4:$C$200))*('sheet1'!$L$4:
$L$200="ip"))


"ub" wrote:

Hi I have using array formula
=COUNT(IF(('sheet1'!$C$4:$C$200="corporate")*('she et1'!$L$4:$L$200="ip"),'sh
eet1'!$Q$4:$Q$200))
I want to trim all extra spaces in column c.
Please advise how to use trim function.
Thanks