SOLVED

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

Occasional Contributor

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

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

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

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

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

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.

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

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!

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

@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 (Occasional Contributor)
Solution

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

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

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

@Ramiz_Assaf

Really grateful thanks for the assistance.

Regards,

Kwabena.

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

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

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

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

so please tell me if the numbers make senese

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

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.

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

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

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

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

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

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!

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

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.

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

@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.

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

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.

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

@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.