asked on

Excel - nested formulas problem

Doesn't Trim work within formulas?

I got a SUMIF like this
=SUMIF($A$18:$A$1006,A2,D$18:D$1006)

It looks like some of the text in the range $A$18:$A$1006 have trailing zeros! So they are not included in the SUMIF.
I tried this, but it doesn't like it
=SUMIF(Trim($A$18:$A$1006),A2,D$18:D$1006)

Why???? Isn't that just a nested formula?

Thanks

Subodh Tiwari (Neeraj)

You cannot manipulate the range passed inside a SUMIF formula. You may try SUMPRODUCT instead for that purpose.

=SUMPRODUCT((TRIM($A$18:$A$1006)=A2)*(D$18:D$1006))

Open in new window

hindersaliva

ASKER

I don't get it. Why can we put a Trim in a SUMPRODUCT, but not a SUMIF?
Is my syntax incorrect?

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

You might be able to use a wild card on the A2 value:

=SUMIF($A$18:$A$1006,A2&"*",D$18:D$1006)

That will sumif anything that starts the same as A2

hindersaliva

ASKER

Rob, that's useful to know. And it might have worked in this case. But I notice that some of the criteria has other characters following (and not just a space).

hindersaliva

ASKER

Thanks Subodh.

Rob Henson

Thanks for the feedback and bonus points.

BTW TRIM function will only remove leading spaces, multiple spaces within text (leaving single space) and trailing spaces; not trailing zeros as per question. TRIM will also not deal with other non-printing characters, CLEAN function will deal with some but again not all.

Subodh Tiwari (Neeraj)

You're welcome!

hindersaliva

ASKER

Oops, sorry Rob! In the question I meant trailing spaces!