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.

As of Date	10/30/2019
Customer ID	Customer Name	Type of Purchase	Date of Purchase	Start Date	End Date	Amount	Current Amount
1	Jane Doe	New	1/1/2018	1/1/2018	12/31/2018	500.00	-
1	Jane Doe	Renewal	1/1/2019	1/1/2019	12/31/2019	500.00	500.00
2	John Barry	New	1/1/2019	1/1/2019	12/31/2019	1,000.00	1,000.00
2	John Barry	Expand	1/1/2019	1/1/2019	12/31/2019	500.00	500.00
3	Jack Smith	New	1/1/2018	1/1/2018	12/31/2018	250.00
3	Jack Smith	Expand	1/1/2018	1/1/2018	12/31/2018	600.00
4	Sue Nelson	New	1/1/2017	1/1/2017	12/31/2017	200.00
4	Sue Nelson	Renewal	1/1/2018	1/1/2018	12/31/2018	200.00
4	Sue Nelson	Renewal	1/1/2019	1/1/2019	12/31/2019	200.00	200.00
5	Mike Plain	New	1/1/2018	1/1/2018	12/31/2018	875.00
	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. Status 2017 2018 2019 New 200 1,625 1,000 Current 200 2,225 1,000 Retention 100% 137% 100% <tbody> </tbody> 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

<tbody>
</tbody>

 

[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.

Status	2017	2018	2019
New	200	1,625	1,000
Current	200	500	1,000
Retention	100%	31%	100%

<colgroup><col span="2"><col span="2"></colgroup><tbody>
</tbody>

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.