SOLVED

Request for assistance on how to use sumproduct to calculate counts of Full and partial payment

Copper Contributor
Hello All,
 
I would like your assistance on a calculation on how to count the number of full or partial payments for a set of figures which has an expected amount against the actual payment.
 
I would be very grateful if you can assist. Below is my formular please confirm if there are any changes I have to initiate.
 
=SUMPRODUCT(((('Drop offs'!J:J='Payment summary'!B4)+('Drop offs'!AT1='Payment summary'!C3)+('Drop offs'!AT:AT/'Drop offs'!AP:AP<=100%))))
 
Regards,
Kwabena.
17 Replies

@Kwabs 

 

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

@Ramiz_Assaf 

 

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.

@Kwabs 

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!

@Ramiz_Assaf

Thanks for the response but what i want is a formula that would calculate the count out of the column P (June-2020) divided by column N which would display as count of 264 for full payment and 16 for partial payment without necessarily depending on the formulas in columns (X:X ) and columns (Y:Y). I want the formulas to stand on it own without a helper column. Hope this finds you well.

Regards,
Kwabena.
best response confirmed by Kwabs (Copper Contributor)
Solution

@Kwabs 

 

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 :)

 

@Ramiz_Assaf

 

Really grateful thanks for the assistance.

 

Regards,

Kwabena.

@Ramiz_Assaf 

 

Thanks again the columns are those highlighted in red and yellow on their respective tabs.

@Kwabs 

 

Please find the modification, but yet I am understanding this application 100%

so please tell me if the numbers make senese

@Ramiz_Assaf 

 

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.

@Kwabs 

 

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

@Ramiz_Assaf 

 

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

@Kwabs 

 

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!

@Ramiz_Assaf 

 

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.

@Ramiz_Assaf

 

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.

@Kwabs 

 

I will try to help you, hopefully by tonight

thanks for your patience 

 

@Kwabs 

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.

@Ramiz_Assaf

 

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.

1 best response

Accepted Solutions
best response confirmed by Kwabs (Copper Contributor)
Solution

@Kwabs 

 

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 :)

 

View solution in original post