Formula to sum based on multiple criteria -For subscription revenues

[tarinann]

Hello Excel Community,

I was wondering if any of the amazing Excel masterminds can help me with a fomula based on several criteria.

First I have a table with date which includes a customer ID, Customer Name, Type of Purchase, Date of Purchase, Start Date and End Dates of Purchase, the amount of the order and the current amount (meaning the total amount of the purchase if it is not expired) as of a particular date in time, which can be changed as needed.

Below is my sample data to be used for the formula I am trying to create.


[TABLE="width: 811"]
<tbody>[TR]
[TD]As of Date [/TD]
[TD="align: right"]10/30/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer ID[/TD]
[TD]Customer Name[/TD]
[TD]Type of Purchase[/TD]
[TD]Date of Purchase[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Amount[/TD]
[TD]Current Amount[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Jane Doe[/TD]
[TD]New[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD] 500.00[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Jane Doe[/TD]
[TD]Renewal[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD] 500.00[/TD]
[TD] 500.00[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]John Barry[/TD]
[TD]New[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD] 1,000.00[/TD]
[TD] 1,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]John Barry[/TD]
[TD]Expand[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD] 500.00[/TD]
[TD] 500.00[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Jack Smith[/TD]
[TD]New[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD] 250.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Jack Smith[/TD]
[TD]Expand[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD] 600.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Sue Nelson[/TD]
[TD]New[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD] 200.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Sue Nelson[/TD]
[TD]Renewal[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD] 200.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Sue Nelson[/TD]
[TD]Renewal[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD] 200.00[/TD]
[TD] 200.00[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Mike Plain[/TD]
[TD]New[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD] 875.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

Below is my desired output. I am looking for a formula that will return the current column from the data in the table above. I do not have VBA skills, so a formula solution is preferred, if possible.

[TABLE="width: 440"]
<tbody>[TR]
[TD]Status[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[/TR]
[TR]
[TD]New[/TD]
[TD] 200[/TD]
[TD] 1,625[/TD]
[TD] 1,000[/TD]
[/TR]
[TR]
[TD]Current[/TD]
[TD] 200[/TD]
[TD] 2,225[/TD]
[TD] 1,000[/TD]
[/TR]
[TR]
[TD]Retention[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]137%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]



The new column is fairly easy, as I can just type a formula to find type new between a date range and get the result.

The current column is where I am having difficulties, as I would like the sum of the current amount for all customers that were new between the original date range. The line of the original date range is not current, but expand and/or renewal lines in new date ranges contain the current amount.


Thanks in advance for any guidance/help!!

Tammy

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Sam_D_Ben]

It would be helpful if you show us the required output.

 

[tarinann]

Hi Sam,

Thanks. The required output is listed in the second table above. I am trying to calculate the results in the row that shows “current” amounts under each column year.

Basically, I want to sum the “current” column for all of the rows for customers who had a type of “new” between the dates in the start and end date columns.

I hope this helps.

 

[SpillerBD]

SUMIFS is the base function to use

 

[Sam_D_Ben]

SUMIFS is the base function to use

I guess so.

But how did the OP got Current 2225 for the year 2018.

 

[tarinann]

SpillerBD,

Thanks. That is where I started but I want to sum the rows for all customer rows that had NEW in a row that corresponds to the date range in the start and end date columns. With SUMIFS, I would only get the current amount for the single row that contained the type NEW and the start and end dates. I want all rows for the current column for any customer who had new in the date range filter.

 

[tarinann]

Sam,

Great question. Actually the $2,225 should be $500, which the sum of the current amount column for customers that had a type of "new" between the start date of 01/01/2018 and 12/31/2018. See the updated desired output below.

[TABLE="width: 440"]
<colgroup><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]Status[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[/TR]
[TR]
[TD]New[/TD]
[TD] 200[/TD]
[TD] 1,625[/TD]
[TD] 1,000[/TD]
[/TR]
[TR]
[TD]Current[/TD]
[TD] 200[/TD]
[TD] 500[/TD]
[TD] 1,000[/TD]
[/TR]
[TR]
[TD]Retention[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]31%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]

Jane Doe had a New purchase of $500 in this time frame. Her "current" amount is $500
Jack Smith had a New purchase of $250 in this time frame, but his current is zero
Mike Plain had a New purchase of $875.00 in this time frame, but his current is zero

 

[tarinann]

Thanks for all of the suggestions. I figured out a way to accomplish this with a sumproduct function and table.

 

[Sam_D_Ben]

Could you please post your findings and solutions here. would be helpful for me. thanks.

I tried SUMPRODUCT from my end too, it dint work for me.

Thanks again.