# TRIM Function before SUMIF?

#### foustus82

##### Board Regular
I have a dashboard that is fed from a data connection to another workbook. The feeder workbook has certain columns with monetary values in them and a dollar sign (\$) transfers to the dashboard tables upon data refresh. However, because of the dollar sign being pulled in, the SUMIF function will not work (because it doesn't know what to do with a dollar sign - it is an actual dollar sign, not simply a cell format). I have no control over the format of the feeder workbook, so I cannot remove the dollar sign in front of the number. I'm looking to see if there is a way to create a formula that will accomplish the SUMIF that I need, but perhaps also incorporate a TRIM or LEN formula to get it to ignore the dollar sign. Any thoughts?

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### cyrilbrd

##### Well-known Member
Hi, you could use something such as =SUBSTITUTE(A1,"\$","")*1 and sum the newly created column.
Or if you do not want any helper column use something such as =SUMPRODUCT(SUBSTITUTE(A1:A6,"\$","")*1) Here A1 to A6 holds your data.

Would that work for you?

#### foustus82

##### Board Regular
Hmmm, I don't think that will address the issue. what has me puzzled is that the workbook that the data originates has the column I'm connecting formatted as "Accounting". I have the data connection properties set to keep the formatting of the feeder workbook, so, it should be able to SUMIF no problem. If I create a random couple of columns and format it to accounting, I run the SUMIF and it works. So, I wonder if the data I'm importing is having an issue...they are formatted, generally, as tables...not sure if that would make a difference or not.

#### cyrilbrd

##### Well-known Member
your data is in a table? each cell is an integer and not a text entered as a number?

#### foustus82

##### Board Regular
That's correct. Some cells have text, some have numbers. But, nothing is set up as "numeric" but shows a "text" or vice versa. After looking at this more, perhaps my initial idea is off. It appears that the issue may lie in the table itself.

##### MrExcel MVP
what does =istext() return for your 'numbers'?

#### foustus82

##### Board Regular
It returns FALSE, on both the feeder workbook and the recipient workbook

#### foustus82

##### Board Regular
Ok, after looking at it again, I see that it is indeed putting the \$ sign in the cell (not a matter of formatting) (it must have been getting late last night lol). So this will remove the dollar sign for me:

TRIM(RIGHT(MTD!I4, LEN(MTD!I4)-FIND("\$", MTD!I4, 1)))

Now, I need to figure out how to incorporate this into a SUMIF or SUMIFS or SUMPRODUCT formula.

#### foustus82

##### Board Regular
Just figured it out. This is quite a monstrosity of a formula

=SUMPRODUCT(IF(MTD!E:E=C15, (SUM(TRIM(RIGHT(VLOOKUP(C15,MTD!\$E:\$I, 5, 0), LEN(VLOOKUP(C15,MTD!\$E:\$I, 5, 0))-FIND("\$", VLOOKUP(C15,MTD!\$E:\$I, 5, 0), 1))))),0))

#### cyrilbrd

##### Well-known Member
Just figured it out. This is quite a monstrosity of a formula

=SUMPRODUCT(IF(MTD!E:E=C15, (SUM(TRIM(RIGHT(VLOOKUP(C15,MTD!\$E:\$I, 5, 0), LEN(VLOOKUP(C15,MTD!\$E:\$I, 5, 0))-FIND("\$", VLOOKUP(C15,MTD!\$E:\$I, 5, 0), 1))))),0))

=VALUE(SUBSTITUTE(A1:A6,"\$","")) should suffice instead of TRIM(RIGHT(MTD!I4, LEN(MTD!I4)-FIND("\$", MTD!I4, 1))) no?

Replies
3
Views
118
Replies
5
Views
541
Replies
0
Views
278
Replies
13
Views
1K
Replies
1
Views
716

1,172,173
Messages
5,879,463
Members
433,434
Latest member
skk0048

### 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.

### Which adblocker are you using?

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

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