TRIM Function before SUMIF?

foustus82

Board Regular
Joined
Jan 6, 2014
Messages
59
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
Joined
Jan 6, 2014
Messages
59
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
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
your data is in a table? each cell is an integer and not a text entered as a number?
 

foustus82

Board Regular
Joined
Jan 6, 2014
Messages
59

ADVERTISEMENT

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.
 

foustus82

Board Regular
Joined
Jan 6, 2014
Messages
59

ADVERTISEMENT

It returns FALSE, on both the feeder workbook and the recipient workbook
 

foustus82

Board Regular
Joined
Jan 6, 2014
Messages
59
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
Joined
Jan 6, 2014
Messages
59
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
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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?
 

Forum statistics

Threads
1,136,354
Messages
5,675,303
Members
419,560
Latest member
g3org

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
Top