Complex SUMIF (array?) formula with several sheets-looking

**Shok** · 08-08-2013, 06:53 AM

Dear Excel maniacs,

I need to do the sumif of certains cells combined with some kind of Vlookup in separate sheets, with duplicate values included.
I have three lists for that. The objective is to add all values per customers according to its group.

I have attached a file that describe exactly the problem (the only difference is that the lists are on the same sheet) I face to help you understanding my task.
I need to fill I4 and I5.

Sum help.xlsx

Basically :
- List 1 is where all customers are listed with their value. It is an automatic list and I can not edit it.
- List 2 is a corresponding table customer/group which is supposed to help finding the customer reference. It cannot be edited. Please note that the same customer can appear several times.
- List 3 is a consolidated view of the groups, where I have to do the sum of the customers value.

I cannot use a pivot table or VBA for doing the job. I need a formula... I have tried to look for a SUMIFS or some weird array formula, but my knowledge of Excel is way too limited to do that. Thanks a lot in advance for your kind help !

**Shok** · 08-09-2013, 09:36 AM

Up !

and EDIT : it is possible to add columns to the second list to make an intermediate step, but I still don't know how a SUM IF function can do at the same time something like a vlookup

**GeneralDisarray** · 08-09-2013, 09:48 AM

This is a perfect one for sumproduct() but in order to make that happen you need to have list of the same length to work with.

See attachment.

If you need help using this for the real ranges you are dealing with please upload a more accurate example. I would suggest using named formulas to handle the real ranges that I have "dollar signed" for ease of understanding when you read the formula.