makako
08-30-2006, 05:18 PM
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
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