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?

On Jun 5, 8:59*pm, GavinS wrote:
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.

GavinS wrote:
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.

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

Thanks


On Jun 6, 1:41*pm, joeu2004 wrote:
On Jun 5, 8:59*pm, GavinS wrote:

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.

GavinS wrote:
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.