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 €​

 

[megaman110]

Of course Peter. Thanks in advance. In addition to the original table I have added another criteria ("Customer segment") which reflects the size of the customer (Small/Medium/Large). What I would now like to do is calculate the monthly revenue changes (again with new, upsell, downsell, lost) but only for one specific customer segment. In this specific the Small customer segment. This in the end allows me to analyze whether the company is performing better/worse in specific customer segments compared to others.

In addition to the original table, I have added

 

[Peter_SSs]

Thanks for the samples. See how this goes.

megaman110.xlsm
	A	B	C	D	E
1
2
3	Cust 1	Large	100	150	200
4	Cust 2	Medium	100	50	25
5	Cust 3	Small	50	70	70
6	Cust 4	Small	20	20	10
7	Cust 5	Small	0	30	0
8	Cust 6	Large	0	100	200
9	Cust 7	Small	0	0	20
10
11
12
13	Small	New	70	30	20
14		Up	0	20	0
15		Down	0	0	-10
16		Lost	0	0	-30
Sheet1

Cell Formulas
Range	Formula
C13:E13	C13	=SUM(FILTER(C3:C9,(IFERROR(--B3:B9,0)=0)*($B3:$B9=$A$13),0))
C14:E14	C14	=SUM(IF($B3:$B9=$A13,IF(C3:C9>B3:B9,IF(B3:B9>0,C3:C9-B3:B9,0),0),0))
C15:E15	C15	=SUM(IF($B3:$B9=$A13,IF(C3:C9<B3:B9,IF(C3:C9>0,IFERROR(C3:C9-B3:B9,0),0),0),0))
C16:E16	C16	=-SUM(IF($B3:$B9=$A13,IF(C3:C9=0,IF(B3:B9>0,B3:B9,0),0),0))

 

[megaman110]

Thanks Peter the excel god life changer for me haha

 

[Peter_SSs]

Cheers, glad to help.