Forum Discussion

Kwabs's avatar
Kwabs
Copper Contributor
Sep 01, 2020
Solved

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.
excel
  • Ramiz_Assaf's avatar
    Ramiz_Assaf
    Sep 02, 2020

    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 🙂

     

17 Replies

  • Ramiz_Assaf's avatar
    Ramiz_Assaf
    Iron Contributor
    Sep 02, 2020

    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

    • Kwabs's avatar
      Kwabs
      Copper Contributor
      Sep 02, 2020

      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.

      • Ramiz_Assaf's avatar
        Ramiz_Assaf
        Iron Contributor
        Sep 02, 2020

        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!

Resources