Sumproduct with Trim

[Katy Jordan]

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))

 

[Katy Jordan]

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.

If i was to add another criteria to this then i did this, the result returned 0.00 but when put as indivduals they return corrrect result. So why 0.00 when combined?

=SUMPRODUCT(--(TRIM(Sheet1!C1:C65000)="INTEREST"),
--(TRIM(Sheet1!C1:C65000)="INT.ACC"),Sheet1!D1:D65000)

 

[Katy Jordan]

But this seems to work with SUMIF, bizzare

=SUMIF(RawData!C:C,"*" & "INTEREST" & "*",RawData!D:D)+SUMIF(RawData!C:C,"*" & "INT. ACC" & "*",RawData!D:D)

 

[danzon]

"INT.ACC"
vs
"INT. ACC"

but as previously mentioned, SUMIF is more efficient than sumproduct

 

[Jon von der Heyden]

Try:

=SUM(SUMIF(A:A,{"*interest*";"*int.acc*"},B:B))