Formula Help Needed - Sumifs or Index Match - Text and number formats concatenat

I am having trouble with w formula to setup a spreadsheet to automate the task of preparing a financial statement. The description of the problem is below.

Concatenate:

I provided a snapshot of the data below. I am using the following formula =CONCATENATE(A190, "."&B190, ""). I need to have a second period between the Business Unit Column data and the Object account data without inserting the period if Col 3 is blank. The reason is that the spreadsheet uses a sumifs formula to lookup data from 3 separate tabs and a value will not return if Col 5 shows 1003821.426.

Is there a condition you can place on the concatenate formula to solve this?


Col 1 Col2 Col3 Col4 Col5 (concatenate formula)
Business Unit Object Account Subsidiary Account Description Acct Conc
1003821 426 Miscellaneous Golf Revenue 1003821.426
1003821 426 06 Golf Dues 1003821.426

Sumifs with text and number formats:

The spreadsheet uses a sumifs formulas to lookup data based on col 5 as appropriate from three worksheets and places the value in col 6-11. This works great using the concatenate formula above for the rows without any value in col3. However, the rows with a col3 value raw in text format while the rest of the data is number format. I have tried hard keying the acct in col5 and it still does not return a value using sumifs. Even if I convert the data values to number format, I still get a 0 in this case. Is there a different formula I can use that will work around this? Match Index perhaps? Not too familiar with the formula but read that may help.

Unfortunately I cannot share the spreadsheet because of proprietary info. Any help is appreciated.

Hi David,

The way your sample data posted, it is impossible to work out what you're attempting.

Try posting with CODE tabs (use the hash icon (#) - sorry that's an Aussie word, I think you call it pound).

Regards,

David

Thanks for the replies. The substitute/trim formulas worked well for my concatenate issue.

Still have an issue with the sumifs formula.

I have attached a small example. The data tab is pulling in info from the 3 TB by subldgr tabs. The issue is that for the accounts with the xxxxx.xxx.xx format is not capturing the info. Not sure what the deal is because I have messed with text vs number format, ect. The TB by SubLdgr worksheets are direct downloads from the financial system and must have something to do with formatting.

Thanks again for the feedback

Hi David,

The problem seems to be that the Account Number that is being looked at has a number of leading blanks.

TRIM() doesn't seem to work inside SUMIFS, so I created a helper column using TRIM() to get rid of the leading blanks, and it worked.

Have a play, and let me know if you can solve it.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".