Sumifs not working in formulated columns

[DGT007]

Hi,

I am trying to use sumifs to pull through the totals from a list of accounts. There are numerous of the same account with different amounts in and I need all of them to pull through.

I think the issue stems from the amounts being formulated using a trim and substitute formula.

I've created a spreadsheet which pulls information from other workbooks then separates out the details pulling through into separate columns.

The function used to separate the information is =IF($I1961="","",TRIM(MID(SUBSTITUTE($I1961,"*",REPT(" ",999)),COLUMNS($I:P)*999-998,999)))

This gives me the total in that cell. When I do a sumifs for this column against the account codes it pulls them through as 0.

Is there a way I can sum the the values against the codes?

 

[DGT007]

I've managed to get round the issue using a 'helper' column with =value formula. Is there a better way to do it though?


Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

Is there a way I can sum the the values against the codes?

Almost certainly: "Yes"

I think the issue stems from the amounts being formulated using a trim and substitute formula.

Almost certainly you are correct.

Before that though there may be a better way to isolate the required data since you have Microsoft 365.
Any chance the you could give us a small amount of sample data (including your current formula) with XL2BB and explain again in relation to that sample data?

 

[kvsrinivasamurthy]

Try this

=IF($I1961="","",IFERROR(VALUE(TRIM(MID(SUBSTITUTE($I1961,"*",REPT(" ",999)),COLUMNS($I:P)*999-998,999))),0))