Using TRIM to remove spaces in a CriteriaRange

[RicktRoo]

My logic is in the "E" column and I attached a dummy test file. If you add a space in the "B" column after "Paws " it can't do the sum of the "A" column. Some special TRIM logic has to be used to use TRIM to trim the entire B column first before it adds the "A" column for any cell that equals exactly "Paws" (I've experimented and the case of text in "B" does not matter -- because if "paws" were found in "B" it would add "A" ). I've only added my logic in 5 of the "E" cells but eventually once I know what the correct TRIM logic is I would add logic in all of those "E" cells.

Somehow TRIM has to be used in my logic: =SUMIF(B6:B51, "Paws", A6:A51) -- again, this works fine if "Paws" or "paws" is found in B6:B51 "Paws " does not work !!

I just know there has to be some way to insert TRIM in my logic but I'm not sure how to.

 

[AliGW]

Try this:

=SUMPRODUCT((TRIM($B$6:$B$51)=TRIM(F30))*$A$6:$A$51)

or this:

=SUMPRODUCT((TRIM($B$6:$B$51)="Paws")*$A$6:$A$51)

BUT the correct way to deal with this is to cleanse your data, not work around it.

 

[vletm]

RicktRoo​

cell E6 =SUM(FILTER(A$6:A$51,TRIM(B$6:B$51)=F6,0)) and copy down

 

[RicktRoo]

Try this:

=SUMPRODUCT((TRIM($B$6:$B$51)=TRIM(F30))*$A$6:$A$51)

or this:

=SUMPRODUCT((TRIM($B$6:$B$51)="Paws")*$A$6:$A$51)

BUT the correct way to deal with this is to cleanse your data, not work around it.

1/7/2025

Thank you VERY MUCH

AliGW​

I had surgery since I submitted by problem and have been VERY busy - VERY trick formula / code !! I'm trying to figure out exactly what you are doing. I will try and get back with you.

=SUMPRODUCT((TRIM($B$6:$B$51)=TRIM(F30))*$A$6:$A$51) --> this works perfectly !!

Thank you for your time.

 

[AliGW]

You're welcome - feel free to LIKE my solution.