Sum payments based on payment received range, count payments based on ID and package

Hello,
I am trying to do 3 things with my data: 1. Sum my Payments Received based on a specified range: < 30 days, between 30-59 days, between 60-90 days and > 90 days based on today's date; 2. Count the number of payments within each range; 3. Count the number of packages within each range (payments are grouped by package ID). I have tried SUMIF and SUMPRODUCT but neither is working, I know I am doing something wrong, just not sure what it is. Any help would be greatly appreciated. I am using Excel 2013.

Attached is a sample project and below is the table I am trying to fill in.


Payments Received Range Amount Paid Invoice Count Package Count
< 30 days
30-59 days
60-90 days
> 90 days

Following are formulas that will work for Amount Paid and Invoice Count respectively:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

The references to column J are for the bin numbers (ranges). In order to understand what a formula for Package Count could be it would be helpful if you would provide the values that you would expect to see in that column.
Let us know if you have any questions.

Thank you very much for your response! It worked perfectly for Amount Paid and Payment Count. I had to alter the formula for Payment Count to 9000 from 99^99 but it gave me the correct count. I have edited my sample workbook to better reflect what I am trying to accomplish with Package Count. Some invoices are paid with multiple payments and I am trying to count how many invoices have been paid within the specified ranges. This will result in double counting in cases where an invoice has multiple payments that span different ranges but in the case that my invoice does not span different ranges, I only want it counted once. Please see the attached, updated sample data.

Thank you for your assistance.

It would help if you could manually enter the numbers that you expect a formula to display for the range H3:H6.

Added another helper column (J) which is populated using the array entered formula:

Formula:

Please Login or Register  to view this content.

Note that J6 is 'hard wired' with the number 2 which is assumed to always be the first date in the range.
Column H is then populated with the formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Those numbers represent the row in which the 'Payment Received Range' changes. If you change the date in B10 to the 15th of August the row reference in J5 changes to 10 because the 'PRR' now changes from >90 days to 60-90 days at row 10.
If that takes care of your question and the formulas are working as desired, please select Thread Tools from the menu link above your first post and mark this thread as SOLVED. Thank You for the feedback. I hope that you have a blessed day.

You're Welcome