countif/sumif with more criterias - too complex for countifs/sumifs

Hi all,

please find attached a sample worksheet I prepared to help me explain my problem. It is a bit more complex so please bare with me. I have a table consisting of 18 fixed columns. Column A, E and R show the 3 different customers, 3 different areas and 11 different classes, respectively.Duplications can occur in these columns. Columns B - D and F - Q consist of important data which can be neglected for now but cannot be deleted. The 3 tables below illustrate the prices for each customers depending on the classes. I would like to find out how much money is needed in each area. I manually calculated what the results should be.

It is very important that in the original worksheet there are 3 different customers, 26 different areas and 14 different classes.

thanks in advance for your help.

I am looking forward to hearing from you.

The format you have your customer pricing data in is making this task significantly harder than it needs to be.

I've put it in a more accessible format and added the formula you need - would this be OK for you?

Hi guys,

thank you so much for your help! You are amazing! The second solution Andrew-R suggested works on the sample worksheet but when I implemented it into my original worksheet #N/A appears. I put the extra column S in a completely new worksheet. I have t say that the big table is on one sheet, the customers are on a second and the end result is on a third sheet. Am I running into problems with the offset there?

Thanks in advance.

The formula I suggested needs to include the sheet reference. I've put demonstration ones in the formula below:

=INDEX(OFFSET(Sheet2!$B$19:$B$29,0,MATCH(A3,Sheet2!$17:$17,0)-1),MATCH(R3,OFFSET(Sheet2!$A$19:$A$29,0,MATCH(A3,Sheet2!$17:$17,0)-1)))

Does that help?

Edited to add: In case it's not clear, that refers to the sheet where your customer lookups are. The ranges will also need to be adapted.

Guys,

it worked. I had a format error which I now solved. Thank you so much! Happy new year!