Sum product & indirect help

[Jazzer]

Hi Guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am currently using the following Sum product<o></o>
<o></o>
=SUMPRODUCT((LCHDATA3!E10:E50="EUR")*(LCHDATA3!D10:D50="H")*(LCHDATA3!H10:H50="INTEREST")*(LCHDATA3!M10:M50))<o></o>
<o></o>
I need to amend the fact that the result will come either from column L (if a debit) or column M (if a credit) -- not just column M as the current formula states<o></o>
<o></o>
Can anybody help with this please?<o></o>
<o></o>
Also as the data is deleted and replaced on a daily basis would you be able to add the INDIRECT function to my formula in order to stop the REF error from occurring please?<o></o>
<o></o>
Many Thanks<o></o>
James<o></o>

 

[Jonmo1]

Since you describe it as a debit vs credit, can we assume that for any given row, there will only be a value in L OR M, not Both?

If So, try

=SUMPRODUCT((LCHDATA3!E10:E50="EUR")*(LCHDATA3!D10:D50="H")*(LCHDATA3!H10:H50="INTEREST")*(LCHDATA3!L10:M50))


As for using indirect, yes it's possible but will be EXTREMELY cumbersome and inefficient.
I recommend you evaluate your process of replacing/adding new data.
Instead of DELETING data, try CLEARCONTENTS instead
Then paste new data and sort, this will move any blanks to the bottom.


Hope that helps.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>