How to perform various calculations and combining two variables

[Even]

Hi, I am stuck with what seems to be an impossible task - so I need some help from you guys! I need to do different calculations in the attached sheet. For example, I need to sum the disbursements for each customer (col A). This is easy, however, I need to separate by the specific type (col B). In addition, I need to combine the two types (col B) for each customer and find the sum disbursement. However, there are main types of categories that need to be taken into consideration. Type 1 and 2 are two main categories and I need to sum those main categories with the sub categories for each customers. For example; customer 2 has type 1 and 3, and thus I need to sum those two categories (col L). Customer 3 has type 1 and 9 and thus I need to sum those two categories. And customer 4 has type 2 and 9 and I therefor need to sum those categories. There are 4 main categories, and I need to the same thing for those. This is just a sample - the real file has over 4000 rows. Does anybody know what I am talking about and can anybody help?

I appreciate the help !

[JosephP]

I'm not sure but maybe you just want a pivot table? perhaps you could change your example file to show what results you actually want to see?

[Even]

Thanks for the reply. The sample is actually a real extraction from the file which is much bigger (thought it would be easier to narrow the amount of data).

[JosephP]

I mean we need to see what final result you want-that is not obvious to me from your file

[Even]

Sorry, I misunderstood. The sum for customer 2 should be 1 722 881 (combining type 1 (which is the main type) and 3 (which is the sub type)). For customer 3 the sum is 583 476 (combining type 1 and 9). I want to do this with all the customers who have the main type 1 in combination with any other sub type they might have. Furthermore, I want to do the same thing with the other main types in combination with its sub types. For example customer 4 who has the main type 2 together with the sub type 9 the sum is 1 150 816. I have classified which type is main type and which is sub type.
In addition to this, I want to sum for each year.

[JosephP]

it would really help to see the output you want in your sample file. from your description it currently seems like you just want a total by year by customer as the types just seem to be combined

[thekops8]

try use this:

=SUMIFS(L:L,B:B,"<>2",B:B,"<=3",A:A,"2")

[Even]

Thanks, but it's not quite what I am looking for. I have included a sheet1 to show the final output what I am trying to explain. In B1 I type in the customer number and the info for that customer comes in row 4 while the average of all customers comes in row 5. Moreover, I show info for each year for the specific customer and for all the customers in average. All this is in place. It's fairly easy when dealing with only one "type" for instance the main type 1, but the problem is when I want to combine the main type for each customer with the sub type for that customers because each customer might have different main type and sub type. Can this be done and if so how and can I do all this in one operation or do I have to do it in several steps?
Sample1.xlsx

[Even]

Can anybody help me? Maybe I don't explain the problem well enough? Maybe I should repost in a different forum?

[oeldere]

As already mentioned in #2 by JosephP, i think this can be solved with an pivot table.

See the attached file.

[Even]

I see that it solves the problem of combining the types for each customer, so thank you very much. However, I was looking for a way to set it up like the table attached.

[oeldere]

@Even

I only see data on the sheet accident. I don't see a sheet with an table.

I'm missing something?

Please show us the desired result, so we can see what your mean (you're up to).

[Even]

If you click on "sheet1" in the attached file above you'll see the table that I want to be automatically updated when I select a specific customer in "B1".