sumifs with criteria range

[megaman110]

Hello everyone,

I have an issue with the following table: I am looking at a software-as-a-service company and would like to break down their subscription revenue changes Month-over-Month. One key element of this revenue change is "Upsells" which shows the additional revenue generated with exisiting customers. In below table, customer 1 was upsold with €50 and customer 3 with €20. Is there a sumif formula which allows me to aggregate those changes and gives me the total €70?

Thanks for you help, very much appreciated.
Best, Mario

Customer #	Jan/2023​	Feb/2023​
Customer 1	100.00 €​	150.00 €​
Customer 2	100.00 €​	100.00 €​
Customer 3	50.00 €​	70.00 €​
Customer 4	20.00 €​	20.00 €​
Customer 5	0.00 €​	0.00 €​

 

[Peter_SSs]

Welcome to the MrExcel board!

Like this?

23 08 09.xlsm
	A	B	C	D	E
1	Customer #	Jan-23	Feb-23		Total Change
2	Customer 1	100.00	150.00		70.00
3	Customer 2	100.00	100.00
4	Customer 3	50.00	70.00
5	Customer 4	20.00	20.00
6	Customer 5	0.00	0.00
Sum Change

Cell Formulas
Range	Formula
E2	E2	=SUM(C2:C6)-SUM(B2:B6)

.. or you could just use

=SUM(C2:C6-B2:B6)

 

[megaman110]

Thank you Peter, very helpful. Unfortunately, this doesn't solve my problem. The customer list I need to work on is much more comprehensive, it includes >5,000 customers. Some have a lower revenue the next month, some remain same, some churn to zero, etc. So looking for a formula which works on a large data set.

 

[Peter_SSs]

The size of the data set is irrelevant to that formula.

If there are different circumstances to the ones you included in the original sample, then please provide a new set of sample data that shows the variations possible, the expected results and a fresh explanation that includes the new variations.

 

[megaman110]

The size of the data set is irrelevant to that formula.

If there are different circumstances to the ones you included in the original sample, then please provide a new set of sample data that shows the variations possible, the expected results and a fresh explanation that includes the new variations.

Sure. I have attached a new screenshot. What I need to do in order to calculate certain KPIs is to slice the month-over-month revenue changes into four categories:
1. New customer revenue: Customers that haven't generated revenue the previous month --> Solved via a simple sumif formula
2. Upsell customer revenue: Customers that have generated revenue the previous month but generate a higher revenue in the current month--> formula missing
3. Downsell customer revenue: Customers that have generated revenue the previous month but generate a lower revenue in the current month --> formula missing
4. Lost customer revenue: Customers that have generated revenue the previous month but generate revenue of zero in the current month --> Solved via simple sumif formula

In the past, I have solved this by creating multiple sheets for each of those four revenue changes where I calculated the changes for each customer and then adding up the total monthly change. But I am hoping that there is a better solution to this.

Very much appreciate your help! Thanks, Mario

 

[Peter_SSs]

into four categories:

That is quite a difference from post 1 which only mentioned 'Upsells'.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Here is one way that might suit.

23 08 09.xlsm
	A	B	C	D
3	Cust 1	100	150	200
4	Cust 2	100	50	25
5	Cust 3	50	70	70
6	Cust 4	20	20	10
7	Cust 5	0	30	0
8	Cust 6	0	100	200
9	Cust 7	0	0	20
10
11
12
13	New	270	130	20
14	Up	0	70	150
15	Down	0	-50	-35
16	Lost	0	0	-30
new up down lost

Cell Formulas
Range	Formula
B13:D13	B13	=SUM(FILTER(B3:B9,IFERROR(--A3:A9,0)=0,0))
B14:D14	B14	=SUM(IF(B3:B9>A3:A9,IF(A3:A9>0,B3:B9-A3:A9,0),0))
B15:D15	B15	=SUM(IF(B3:B9<A3:A9,IF(B3:B9>0,IFERROR(B3:B9-A3:A9,0),0),0))
B16:D16	B16	=-SUM(IF(B3:B9=0,IF(A3:A9>0,A3:A9,0),0))

 

[megaman110]

Awesome, thank you very very much Peter! That saves me a huge amount of time. I will use XL2BB going forward.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[megaman110]

Peter, one more question (sorry that I am not using XL2BB but the add-in gets blocked by our IT security software): How would those formulas look like if I wanted to add an additional criteria, such as the customer segment (Small, Medium, Large) and I just wanted to calculate the revenue effects for one specific segment (in this example the small segment)? Thanks a lot, Mario

Customer #	Customer segment	Jan/2023​	Feb/2023​	Mar/2023​
Customer 1	Large	100​	150​	200​
Customer 2	Medium	100​	50​	25​
Customer 3	Small	50​	70​	70​
Customer 4	Small	20​	20​	10​
Customer 5	Small	0​	30​	0​
Customer 6	Large	0​	100​	200​
Customer 7	Small	0​	0​	20​
Total		270​	420​	525​
SMALL
Beginning of Period		0​	270​	420​
New		270​	130​	20​
Upsell		0​	70​	150​
Downsell		0​	-50​	-35​
Lost		0​	0​	-30​
End of Period		270​	420​	525​

 

[Peter_SSs]

As before, could you show your expected results & explain in relation to that?