SUMIFS / Subscription Box Owner

Sum Churned Revenue

I need to sum the 'Monthly Value' of all customers where Status is 'Cancelled' and Reason is 'Price'.

formula.xlsx
=SUMIFS(B2:B9, C2:C9, "Cancelled", D2:D9, "Price")

How it works: The SUMIFS function is used to sum values in a range that meet multiple criteria. In this scenario, it sums the 'Monthly Value' (B2:B9) only for those rows where the 'Status' (C2:C9) is exactly 'Cancelled' AND the 'Reason' (D2:D9) is exactly 'Price'. This allows the Subscription Box Owner to precisely quantify the financial impact of price-related churn.

Data Setup

Customer ID Monthly Value Status Reason
101 25 Active N/A
102 30 Cancelled Price
103 45 Active N/A
104 20 Cancelled Service Quality
105 35 Cancelled Price
106 50 Active N/A
107 28 Cancelled Moving
108 40 Cancelled Price

Step-by-Step Guide

1

Open your Excel spreadsheet containing your customer data.

2

Identify the 'Monthly Value' column (e.g., Column B) which contains the values you want to sum.

3

Identify the 'Status' column (e.g., Column C) which will be your first criteria range.

4

Identify the 'Reason' column (e.g., Column D) which will be your second criteria range.

5

In an empty cell, type the SUMIFS formula: `=SUMIFS(B2:B9, C2:C9, "Cancelled", D2:D9, "Price")`

6

Press Enter to see the total sum of monthly values for customers who cancelled due to 'Price'.