SUMPRODUCT formula not working past a certain point

[hk91]

Hi all, I previously posted a thread asking for a formula. It worked...up until a few rows down and it stopped working. But i can't figure out because I have copied the formula into all relevant cells.

this is the formula I have used:
=SUMPRODUCT((LEFT('Payment Details'!$D$5:$D$1000,7)=$E16)*('Payment Details'!$B$5:$B$1000=F$15)*('Payment Details'!$A$5:$A$1000))

It does not work past row 21 on the 'Banking Sheet'

Any help is greatly appreciated! Thanks,

[XLent]

there's nothing obvious with the ranges nor amounts (given explicit coercion) so this implies either

a) your values in E contain leading / trailing spaces or
b) your values on Payment Details contain leading spaces (Col D), or your Cash values for those rows contain spaces

I would say the most likely cause is likes of E21 containing trailing space(s) - to confirm add a TRIM around the reference to $E16 and copy down.

On an aside, if the tabs are in the same file you should be using SUMIFS as much more efficient, i.e. no obvious requirement for SUMPRODUCT here

Please Login or Register  to view this content.

If, using above pointers, you're unable to resolve post a sample to better illustrate (GoAdvanced --> Manage Attachments)

[hk91]

there's nothing obvious with the ranges nor amounts (given explicit coercion) so this implies either

a) your values in e contain leading / trailing spaces or
b) your values on payment details contain leading spaces (col d), or your cash values for those rows contain spaces

i would say the most likely cause is likes of e21 containing trailing space(s) - to confirm add a trim around the reference to $e16 and copy down.

On an aside, if the tabs are in the same file you should be using sumifs as much more efficient, i.e. No obvious requirement for sumproduct here

Please Login or Register  to view this content.

if, using above pointers, you're unable to resolve post a sample to better illustrate (goadvanced --> manage attachments)

thank you so much!!!!!!!!