SUMIFS with Trim

[GavinS]

On a sheet called Report I have a column of account codes going down
the page. There are no spaces in these codes, they are alpha numeric.

On this Report sheet I have a SUMIFS formula that says
SUMIFS(Transactions!$E:$E,Transactions!$H:$H,$F20,Transactions!$Q:
$Q,Report!R$8)

On a sheet called Transaction is a list of transactions, along with
account code for each line (transaction) in column H. The account
codes in H have trailing spaces.

Using the account code on the Report sheet, I am trying to find
corresponding transactions posted to that account number in the sheet
called Transactions - but teh trailing spaces are causing problems.

To eliminate the effect of the trailing spaces I would like to enter
=SUMIFS(Transactions!$E:$E,Trim(Transactions!$H:$H),$F20,Transactions!
$Q:$Q,Report!R$8)

but this reports an error. Does this need to be an array?.

Is there another way to do this?

 

[joeu2004]

To eliminate the effect of the trailing spaces I would like
to enter
=SUMIFS(Transactions!$E:$E,Trim(Transactions!$H:$H),$F20,
Transactions!$Q:$Q,Report!R$8)
but this reports an error. Does this need to be an array?

No, that does not make it work.

Is there another way to do this?

One sloppy way that might suffice:

=SUMIFS(Transactions!$E:$E,Transactions!$H:$H,$F20&"*",
Transactions!$Q:$Q,Report!R$8)

I say this is "sloppy" because F20&"*" will not only match H:H values
with trailing blanks, but also H:H that might be very different after
the first LEN(F20) characters. For example, if F20 is "A12345",
F20&"*" would match "A123456".

Only you can decide if that is or is not a possibility with your data.

If that is unsatisfactory for that reason, you might try using
SUMPRODUCT. For example:

=SUMPRODUCT((TRIM(Transactions!$H:$H)=$F20)
*(Transactions!$Q:$Q=Report!R$8),Transactions!$E:$E)

Caveat: Since XL2003 SUMPRODUCT does not accept ranges of the form
H:H, Q:Q and E:E, I am unable to test this to be sure that TRIM(H:H)
works as well. In any case, it would be prudent to use finite ranges
such as H1:H1000,Q1:Q1000 and E1:E1000. That form is probably more
efficient anyway.

 

[GavinS]

Yep, that approach
=SUMPRODUCT((TRIM(Transactions!$H:$H)=$F20)*(Transactions!$Q:
$Q=Report!R$8),Transactions!$E:$E)
works.

Thanks