Conditional summation of columns based on category

[Bert Goossens]

Hi all,

I would like to make a sum of different columns based on a property of these columns specified in a list.

In the excel in attachment (simplification of the actual excel) I assign each product to a certain category (which could change) and would like to have a total sum of each category.
I tried using the Choose function with arrays but without success.

I used colors in the excel to point out which values should be summed.

Any ideas? Thanks a lot!

B.

 

[Somendra Misra]

Hi Bret,

Welcome to chandoo forum.

Can you explain where are the categories in the table?

Regards,

 

[Bert Goossens]

Hi!

Thanks for the quick reply.

I've got a list of products (A,B,C,D,...) sales and each product is assigned to a certain category (RS, HNN, WAF,...). The table assigning each product to a certain category is in cells Q4:R8.
Each product total sales consists of the sum of "AR" and "RR" sales, but this is not relevant for the main problem. Only reason why I included this is to show that the different columns for the total product sales are not next to each other.

For each day I would like to have a sum of total sales for each category.

In cell U4 I would like to have the sum of all product totals (in cells D4, G4, J4, M4, ...) for the product category "RS" (cell U2). So in this case D4+G4.

I hope this makes a bit of sense.

Thanks!

Hi Bret,

Welcome to chandoo forum.

Can you explain where are the categories in the table?

Regards,

 

[Somendra Misra]

See if this is OK.

Regards,

 

[Bert Goossens]

See if this is OK.

Regards,

Wow, works perfectly. Thanks a lot! Will take me some time to figure the formula out