Sumproduct with Trim

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
Hi, i want to add trim to the interest as there are trailing spaces in the main data and the formula returns #VALUE, how can i do that,

=SUMPRODUCT((Sheet1!C1:C65000="INTEREST")*(Sheet1!D1:D65000))
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi there,

Try:

=SUMPRODUCT((TRIM(Sheet1!C1:C65000)="INTEREST")*(Sheet1!D1:D65000))
 
Upvote 0
I think in this context you would be better of using SUMIF, it is considerably quicker than SUMPRODUCT and you are passing rather large ranges to the SUMPRODUCT arguments.

=SUMIF(C:C,"*" & "interest" & "*",D:D)

Note, this will sum all entries where the word 'interest' is contained in any text string in column C. If this won't do then I suggest you trim column C first and use an exact match:

=SUMIF(C:C,"interest",D:D)
 
Last edited:
Upvote 0
Hi Katy

Maybe I'm not understanding something but looking at your formula it doesn't seem the problem is the spaces.

Please check the data.

Trailing spaces in the "INTEREST" string will only cause the test (Sheet1!C1:C65000="INTEREST") to be False, not an error.
 
Upvote 0
Hi Katy

Maybe I'm not understanding something but looking at your formula it doesn't seem the problem is the spaces.

Please check the data.

Trailing spaces in the "INTEREST" string will only cause the test (Sheet1!C1:C65000="INTEREST") to be False, not an error.

Good point. It usually means that you have the same error in one of the range references. You can check by highlighting column C/D > hit F5 > Special > Formula (or constants) > leave only Errors checked. That should select errors in the range. Best that you handle errors at the source.
 
Upvote 0
If I were to guess I'd suspect row 1 to be a header row with text in. Try:
=SUMPRODUCT(--(TRIM(Sheet1!C1:C65000)="INTEREST"),Sheet1!D1:D65000)

if you still get #VALUE then you have error values somewhere.
 
Upvote 0
Or use SUMIF because it doesn't give a monkeys if the sum range includes text; and it's much much faster.
 
Upvote 0
If I were to guess I'd suspect row 1 to be a header row with text in. Try:
=SUMPRODUCT(--(TRIM(Sheet1!C1:C65000)="INTEREST"),Sheet1!D1:D65000)

if you still get #VALUE then you have error values somewhere.

Yes that worked
 
Upvote 0
Good point. It usually means that you have the same error in one of the range references. You can check by highlighting column C/D > hit F5 > Special > Formula (or constants) > leave only Errors checked. That should select errors in the range. Best that you handle errors at the source.

When i did this i got a message "No cells were found"
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,717
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top