SOLVED

SOM.ALS to refer to other cells as criterium

New Contributor

Hello everyone, 

I have a problem with the SOM.ALS (SUMIF function in English) function. I have a very large file of numbers, this is a small part of it. If want to know the total amount of days a customer has stayed in a hotel. But some customers have stayed for multiple days (customer 1 has stayed 2 times, 30 days in total). How do I create a formule that I can add up the days in the hotel, dependent on the customer number. 

If now use this: SOM.ALS(A2:A6900; "1"; F2:F6900), but then I have to manually chance the criterium each time. 

 

Customer numberDays in Hotel
11
129
23
31
32
43
55
59
63
61
60
85
91
102
109
109
102
101
101
1 Reply
best response confirmed by CiliaJ1010 (New Contributor)
Solution

@CiliaJ1010 I would create a pivot table. See attached.