Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

0

Hi TeachExcel,

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.

Please assist if there is any other altenative formulas which could be helpful.

Your assistance would be much appreciated.

On standby.

Regards,

Kwabena.

excelformulas-functions
MS Office Version: office 365
posted by: kwabs on Mon Sep 7, 2020 at 3:53 pm
Answer
Discuss

Answers

0

There are some relative references that must be made absolute.

[C8] =IFERROR(SUMPRODUCT('Drop offs'!$H$2:$H$849='Payment summary'!$B$4)*('Drop offs'!K$1='Payment summary'!C$3)+(SUM(IF('Drop offs'!K2:K849/'Drop offs'!$I$2:$I$849>0.99,1,0))),"0")

=IFERROR(SUMPRODUCT('Drop offs'!$H$2:$H$849='Payment summary'!$B$4)*('Drop offs'!K$1='Payment summary'!C$3)+(SUM(IF('Drop offs'!K$2:K$849/'Drop offs'!$I$2:$I$849>0.99,1,0))),"0")

[C9] =IFERROR(SUMPRODUCT('Drop offs'!$H$2:$H$849='Payment summary'!$B$4)*('Drop offs'!K$1='Payment summary'!C$3)+(SUM(IF('Drop offs'!K2:K849/'Drop offs'!$I$2:$I$849>0,1,0)))-C8,"0")

=IFERROR(SUMPRODUCT('Drop offs'!$H$2:$H$849='Payment summary'!$B$4)*('Drop offs'!K$1='Payment summary'!C$3)+(SUM(IF('Drop offs'!K$2:K$849/'Drop offs'!$I$2:$I$849>0,1,0)))-C8,"0")

Here, in addition, the reference to C8 is problematic. Try replacing it with INDEX($A$5:$I$11, MATCH($A8,$A$5:$A$11,0),COLUMN()).

[D16] =IFERROR(SUMPRODUCT('Drop offs'!$H$850:$H$2461='Payment summary'!$B$12)*('Drop offs'!L$1='Payment summary'!D$3)+(SUM(IF('Drop offs'!L850:L2461/'Drop offs'!$I$850:$I$2461>0.99,1,0))),"0")

=IFERROR(SUMPRODUCT('Drop offs'!$H$850:$H$2461='Payment summary'!$B$12)*('Drop offs'!L$1='Payment summary'!D$3)+(SUM(IF('Drop offs'!L$850:L$2461/'Drop offs'!$I$850:$I$2461>0.99,1,0))),"0")

[D17] =IFERROR(SUMPRODUCT('Drop offs'!$H$850:$H$2461='Payment summary'!$B$12)*('Drop offs'!L$1='Payment summary'!D$3)+(SUM(IF('Drop offs'!L850:L2461/'Drop offs'!$I$850:$I$2461>0,1,0)))-'Payment summary'!D16,"0")

=IFERROR(SUMPRODUCT('Drop offs'!$H$850:$H$2461='Payment summary'!$B$12)*('Drop offs'!L$1='Payment summary'!D$3)+(SUM(IF('Drop offs'!L$850:L$2461/'Drop offs'!$I$850:$I$2461>0,1,0)))-'Payment summary'!D16,"0")

You don't need to specify the Payment summary sheet when referring to another cell on the same tab. But the reference to D16 won't work and should be replaced by an INDEX/MATCH function as shown above.

I didn't test because I don't know what the correct results should be. I just picked off what appear to be obvious omissions and hope my effort helps.

posted by: Variatus (rep: 4889) on Tue Sep 8, 2020 at 12:01 am
Discuss

Discussion

Can you assist where exactly i need to apply the index match formula or preferably can you perform one example so i can be really sure because i'm not getting the right results.

On standby. 
Regards
kwabs Sep 8, '20 at 7:30 am
The direct reference to C8 won't work after sorting because the row will be different. The Index/Match formula should replace the "C8". However, to be honest, I wouldn't construct such a formula, and if I would construct it I wouldn't try to sort the result. That's because I have no appetite for testing such a complex formula. My reason is exactly the same as yours. It's terribly hard. But face it, you can do it easier than I because, at least, you know it when you see a correct result, or a wrong one.
Your formula is complicated because it avoids several intermediate steps, meaning it could be made simpler by interjecting a helper table. Perhaps the one you already have could take on such a role. Just create another table from it where you sort the results. That should be comparatively easy just using VLOOKUP.
Variatus (rep: 4889) Sep 8, '20 at 8:03 am
Thanks Variatus. Can you please assist with any other alternative formula?
kwabs Sep 8, '20 at 8:51 am
Sorry, kwabs. I've been dodging the task of trying to understand what your formula wants to extract just as you have evaded to job of explaining what data you wish to it to show and then sort. We both stand in a circle around the formula which you say works but doesn't sort. To my mind that means that your formula works but doesn't sort. In order to make it both work and sort I would need to know what the formula does and how you want to sort. Neither of these are explained. I have made a suggestion which I fully believe that it didn't solve your problem because I didn't test it, nor do I know how to test (can't calculate the correct result) but, anyway, you didn't say how it doesn't work: Doesn't it return the correct result? Does it crash? Or does it fail to sort? And by which criteria to sort, anyway? 
We are far too advanced in this thead to now seek answers to all these questions. Phrase another question in which you implement all you learned form this thread, both about the subject and how to ask a complex question.
Variatus (rep: 4889) Sep 8, '20 at 9:54 pm
Sorry Variatus probably i did'nt state it clearly enough the sorting i meant was about the main data "Drop offs tab". whenever i sort any of the columns maybe the column "H" (Newest to Oldest) the figures under the "#Accounts Collected (Full)" and "#Accounts Collected (Partial)" highlighted yellow on the payment summary keep changing never stays the same. And my request was is there any assistance to fix those formulas. 
kwabs Sep 9, '20 at 6:10 am
Add to Discussion
0

Hello Variatus,

Thanks for the assistance but i have been able to get the right answer to my challenge.

=SUMPRODUCT(('Drop offs'!$AT1:$AZ1=C$3)*('Drop offs'!$AT2:$AZ3081/'Drop offs'!$AP$2:$AP$3081>0.987)*('Drop offs'!$AO2:$AO3081=$B4))

=SUMPRODUCT(('Drop offs'!$AT1:$AZ1=C$3)*('Drop offs'!$AT2:$AZ3081/'Drop offs'!$AP2:$AP3081<0.987)*('Drop offs'!$AT2:$AZ3081>0)*('Drop offs'!$AO2:$AO3081=$B4))

Regards,

Kwabena.

posted by: kwabs on Mon Sep 14, 2020 at 9:27 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login