SUMIFS and OFFSET

[Serhattem]

Hi everybody, i know the topic has been up a few times, but i still cant solve the problem

I am trying to do a sumifs fucktion, where the sumarea is defined as a offset funtion, but it does not work.. Does anyone have a solution?

My formula says:
=SUMIFS(OFFSET(Ind.CC!Q1;(Ind.CC!B25)-1;0;1;9999);Ind.CC!$1:$1;BS$4;Ind.CC!$2:$2;$A$2;Ind.CC!$11:$11;$A$3)
if i just choose row 27 instead of the offset function i does work..

I hope someone can help me

[WHER]

from the "Excel Help":
Unlike the range and criteria arguments in the SUMIF function, in the SUMIFS function, each criteria_range argument must contain the same number of rows and columns as the sum_range argument.
Ranges like $1:$1, $2:$2 are clearly not the same size as "OFFSET(Ind.CC!Q1;(Ind.CC!B25)-1;0;1;9999)"

[Serhattem]

Thanks it works now..

For any interessted, i the right formula is:

=SUMIFS(OFFSET(Ind.CC!Q1;(Ind.CC!B25)-1;0;1;800);Ind.CC!Q1:AEJ1;BS$4;Ind.CC!Q2:AEJ2;$A$2;Ind.CC!Q11:AEJ11;$A$3)

Now both the sum_range and the criteria goes from Q to AEJ (800 columns)

[FDibbins]

Hi, welcome to the forum

why are you using OFFSET for this, what are you trying to do?

[Serhattem]

Hi and thanks

I get some data i have to sort out, but the data does not have the same lenght every time. There is always new models, with different grades.
The data comes in a x and y axis (like a two way tabel)
So i use the Match function to find the row number the item is in (the Ind.CC!B25 in the offset refers to the match)
Then i do a sumifs to get orders of that model in a specific model..

Do you know a easier way to do it?

[FDibbins]

Maybe. Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Serhattem]

Sorry the late answer.. Out server was down yesterday due to some maintaince...How can upload a file?