TRIM Function before SUMIF?

[foustus82]

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?

 

[cyrilbrd]

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]

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]

your data is in a table? each cell is an integer and not a text entered as a number?

 

[foustus82]

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.

 

[PaddyD]

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

 

[foustus82]

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

 

[foustus82]

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]

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]

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?