Sumif 2 criterias one horizontal with duplicates and 1 vertical

Hi there,

I have a table with values. On the y axis i have 3 types (A, B and C). On the x-axis I have calendar weeks (42, 43, 44, 45, 46, 47, 48 and 49). I know how to do a SUMIF for this task. However in my table every calendar week occurs 7 times.

I need to sum all values in the table according to the 3 individual types and 8 individual calendar weeks.

attaches is an excel file which shows the table in sheet1 and the desired output in sheet 2.

The location of the cells must not be changed. Ideally I do not want to use a macro.

Thanks in advance for your help.

SUMIF doesn't work with multiple columns in the sum range, only the criteria range.

There are probably workarounds with OFFSET and INDIRECT but SUMPRODUCT would be easier to implement.

=SUMPRODUCT((Sheet1!$F$6:$F$156=Sheet2!$A2)*(Sheet1!$L$1:$BO$1=Sheet2!B$1)*Sheet1!$L$6:$BO$156)

Originally Posted by bsuperiorsystem

i change 7 column for each week to one week. then i use sumif
any question?

Why use over 1200 formula when you only need 24?

One example in post #2, and another below using your method of sumif with volatile functions that doesn't need those 1200 extra formula.

=SUMPRODUCT(SUMIF(Sheet1!$F$6:$F$156,Sheet2!$A2,OFFSET(Sheet1!$K$6:$K$156,0,ROW(OFFSET($A$1,MATCH(Sheet2!B$1,Sheet1!$L$1:$BO$1,0)-1,0,COUNTIF(Sheet1!$L$1:$BO$1,Sheet2!B$1))))))

@nflsales

Normally I would agree with you there, but I couldn't see any way to replace the first offset with index (or any other non volatile method).
The second offset could be replace with index, but this would increase the number of functions needed in the formula, as it was already volatile with the first offset, I didn't see any point in over-engineering the formula to remove the second one.

guys,

thanks for the effort. I will try your solution later. If they work I will close the threat and add some reputation to your profiles.

Worked! Thanks so much!