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.