Use SUMPRODUCT to calculate counts of Full and partial payments

[Kwabs]

Hello All,

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. 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 a helper column which stands on it own.
I would be very grateful if you can assist. Below is my formula 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%))))

Hope this finds you well.

[FDibbins]

1. Your references in your post dont match what your file shows?

column P (June-2020) divided by column N

June 2020 is col K, not col P, and why would you divide June values with Aug values?

Your posted formula...SUMPRODUCT(((('Drop offs'!J:J='Payment summary'!B4) references B4, but that column is (mostly) empty?

2. you have headings that show eg June - 20202 - what does that represent?

3. You have real dates on Summary sgeet, but you use text in Drop off sheet. When working with dates, you need to try and keep them all dates, that way excel has an easier time comparing them

Take a look at your file...and your post, and try to align things between them please.

[Kwabs]

Thanks FDibbins for the update. I have made some changes to the file kindly review and assist if it finds you well. In terms of the formula i was just trying something which was clearly incorrect.

Please if there is any other formula to arrive at the result highlighted in yellow i would be much appreciative of the assistance.

Attached is an updated version.

on standby.

Regards,
Kwabena

[FDibbins]

You dont really show how you arrive at 264, 16, 11 and 557, and I the closest I can come to 264 is adding J206, J246 ans J277 for 263.55

Walk me through how you arrive at those values please, show me what the criteria are

[Kwabs]

Well noted FDibbins and thanks for the feedback. I have populated a manual criteria from the main data (Drop offs tab) to arrive at the number highlighted in yellow under the payment summary tab where i want a formula that would operate on it own whiles displaying the exact results without necessarily depending on the yellow highlighted columns as helper columns. And also incorporate the column H "Dropped off month" to help breakdown the numbers month on month i.e. linking the column H under 'drop offs tab' to the column C4 under the payment summary tab which help when you drag to the next month on the payment summary you are going to get the right results

Formula walk through: Divide 'j' by 'I' under the drop offs tab ,plus column 'H' equal to Cell C4 under payment summary multiply by column header 'J' equal to column 'D' under payment summary tab.

NB: I Tried the formula below but was given me an error which seems incorrect. Please review and assist if there is any alternative formula to arrive at the correct results

=SUMPRODUCT('Drop offs'!J2:J849,1/'Drop offs'!I2:I849)+('Drop offs'!H:H='Payment summary'!C4)*('Drop offs'!J1='Payment summary'!D3)

Attached is an update as requested.

Your assistance is much appreciated.

Regards,

[Kwabs]

Hello FDibbins,

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.

[JeteMc]

As to the formula in post #5, please try the following:
For cells D8 and over:

Please Login or Register  to view this content.

For cells D9 and over:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Kwabs]

Thanks JeteMc for the update but it seems the results is displaying zero instead of actual counts. I would be very grateful if you can perform some few adjustment on the formula to produce the expected results. Below is snapshot for your attention.

[JeteMc]

I forgot to say that I changed the values in cells J1:L1 on the Drop Offs sheet to actual dates (6/1/2020, 7/1/2020 and 8/1/2020)
With that done I get the same values in D8:E9 as those shown in post #8.
Let us know if you have any questions.

[Kwabs]

Thanks a lot JeteMc the formula is finally working i'm so grateful for your assistance.

Regards,
Kwabena.

[Kwabs]

My challenge has finally been resolved thanks to all.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.