Sumproduct function

[makako]

Recently I read a thread about the countif function that linked me to an article on the sumproduct function working in arrays which was very useful. Now I wonder if its possible to find in a single cell to find the difference between theoretical payments and real payments. In the zip i post there is an example. The T values and the R values are assigned to different codes (clients). and each have an asigned date value. Lets say I have these columns (T) where I keep how the specific code wants to pay me for an Item. The, in columns (R) i keep score of the payments that are real. So, if i want to know how much is missing TODAY i would sum every theoretical payment that i should receive until now and then substract the real payment for each code. Then if the code is missing money (TODAY") then I sum up those leftovers and have an idea of missin payments.
I used before sumproduct((datesT<Now())*(valuesT))-sum(valuesR) but if a code has more money than it should, its exceeding quota covers the missing money of someone else. I tried to make out of the code an array but it didnt work. Thanks

[XLGibbs]

Not sure if I understand completely, but I will take a look and see if I can figure out what it is you intend to do. It "sounds" as if you want to employ additional logic within the sumproduct arrays--

to the point, it sounds like you want the size of the array, and the location of the array itself to vary depending on the day---that can be quite tricky I would think.

Does this have to be a formula solution? or can it be a coded procedure..

[makako]

Yes, the idea is to use the sumproduct and not a coded procedure. The idea is to use instead of using each Code in a separate formula, to use it in an array to have each code have all its T payments before today minus its real payments and sum up the number of codes that owe me money.

[Zack Barresse]

Kind of hard to understand how there can be Real Payments after todays date..

Anyway, I recommend setting up a column for the type (T, R) and creating a pivot table.

[makako]

There are none, in the example I sen maybe there appear some because of the rand function. that should be now()-rand()*360 instead of the one shown, but in the formula u can see I sum up all R payments so one doesnt use the R date. But thanks, at least now I know some one gets the idea

[XLGibbs]

I think Firefytr is correct, a Pivot table is likely the best solution.