Sep 01 2020 12:52 PM
Sep 02 2020 03:35 AM
The issue is with the last argument, I propose create a new column with AP1/AT1...etc then use this new column in the sumproduct.
on another point, why don't you use COUNTIF
Best of luck
Sep 02 2020 04:13 AM
Thanks for the response really grateful.
Attached is file pending the result i'm looking for in the yellow highlighted color. Please review and assist with an alternative solution to the challenge. Your assistance would be very appreciative.
Regards,
Kwabena.
Sep 02 2020 05:09 AM
in the cell D8 of the Payment Summary is used =COUNTIF('Drop offs'!X:X,1)
in D9 I used =COUNTIFS('Drop offs'!X:X,"<1")
I am assuming you need the count of full payments in D8 and partial in D9
tell me is there is something not clear. You are using over complicated formulas
I advise you to revise them, as they are not clear to me why you are using them!
Sep 02 2020 05:35 AM
Sep 02 2020 07:21 AM
Solution
You need to stop using X:X or N:N, I think the problem is the 1st row which is text
N:N should be replaced by N2:N3081 also applies to P:P
then if a client pays 99% or more, you consider him/her full payment, that is why you need to work with >0.99 not 1
see the attached file, hope it solved the problem
if it does, please assign the post as solved and I would love a like :)
Sep 02 2020 07:56 AM
Sep 02 2020 09:26 AM
Thanks again the columns are those highlighted in red and yellow on their respective tabs.
Sep 02 2020 09:55 AM
Please find the modification, but yet I am understanding this application 100%
so please tell me if the numbers make senese
Sep 02 2020 11:39 AM
Attached is an update done manually highlighted in yellow under Jul-2020. Secondly the total number counts needs to sum-up to #Accounts Submitted i.e. 848 under May-2020 which is the actual month under review whiles monitoring it progress months on month.
Hope this finds you well.
Regards,
Kwabena.
Sep 03 2020 12:26 AM
I applied the formulas, even checked the data
If a client paid 98.8 or more he is considered fully paid
They total 405 (based on the data in the column)
I think your number is lower, because you remove those who paid off their loan .. I need help on which column to use
hope this is useful
Sep 03 2020 01:52 AM
Please below is the column that needs to be considered to enable those results
Dropped off Month |
May-2020 |
June-2020 |
June-2020 |
May-2020 |
May-2020 |
Sep 03 2020 10:30 AM
I think you are not explaining the calculations well,
please explain to me one row with its correct calculation. Again the formulas keep change from one row to another. Also I'm not sure if I understand the application correctly. If you like you can send me the details in a private message!
Sep 03 2020 12:16 PM
Attached is an updated file for your assistance. The highlighted column 'H' "Dropped off month" is the one I want Incorporated in the formula so that when you drag the formula to the next column it populates the exact number based on the "Dropped Off Month" as depicted in the payment summary.
Hope this finds you well.
Regards,
Kwabena.
Sep 07 2020 03:44 AM
Hello Ramiz,
It's been a while hope you had a great weekend. I would like to request for an assistance on how to make the columns absolute so when you sort any of the fields, the figures remain the same especially the ones under the "#Accounts Collected (Full)" and "#Accounts Collected (Partial)" highlighted yellow on the payment summary.
Your assistance would be much appreciated.
On standby.
Regards,
Kwabena.
Sep 07 2020 03:55 AM
Sep 07 2020 09:47 AM
It is still a puzzle to me
do you mean you need to be able to sort the columns in the "drop off" sheet.
If yes, the criteria is not clear on how you decide if a record is paid off or dropped.
Sep 07 2020 12:03 PM
What i want is to ensure all the results remain the same or static on the "payment summary" especially the numbers under the full and partial payment when i sort on the "drop off tab". Is there any formula to keep it static?
Your assistance would be much appreciated.
Regards,
Kwabena.
Sep 02 2020 07:21 AM
Solution
You need to stop using X:X or N:N, I think the problem is the 1st row which is text
N:N should be replaced by N2:N3081 also applies to P:P
then if a client pays 99% or more, you consider him/her full payment, that is why you need to work with >0.99 not 1
see the attached file, hope it solved the problem
if it does, please assign the post as solved and I would love a like :)