Help!

[sarai]

I am trying to update customer balances for customers sent to third-party collections. I would like to look up payments for these customers and if they are made after the date they were sent to collections, I would like the sum of the payments to be returned. How can I do that without blowing up my spreadsheet?

Thank you!

 

[Steve=True]

You could use a sumproduct function like such:


=sumproduct((customernamerange=customernamelookup)*(customerpaymentrange))

 

[sarai]

Thank you How can I also add the date critieria? (Only sum those payments made after a certain date).

 

[wigi]

Thank you How can I also add the date critieria? (Only sum those payments made after a certain date).

Do you understand the logic and rationale behind this:

(customernamerange=customernamelookup)

in the formula above? Also, did you look into the possibilities of the * operator, again in the formula above?

 

[Steve=True]

Here is the basis of your formula, but it can be tricky to implement since i am just guessing at your specific data. This is the basic concept.

=sumproduct((customernamerange=customernamelookup)*(customerpaymentrange)*(customerDaterange>=customerDatelookup))