SUMIF with dynamic criteria

[inky]

i'm hoping someone will be able to assist me with my SUMIF problem.

i've attached my sample worksheet and formula yet it's sadly incorrect.

what i'd like to do is, display the results as detailed in rows 13 to 16, being a summary of rows 2 to 10. i have a worksheet that contains 1600 lines of account codes, some repeated, and 250 columns. when an account is repeated in a column, it will only have one instance of data. yet where it is the second instance, a INDEX/MATCH simple stops at the first instance, so where it is 0, it's detailed as 0 yet actually has results (eg P2, P3, P5).

i believe a SUMIF is appropriate as it will add all the instances in a column (as suggested by the formula in C14), with only having to define a few ranges, yet the formula I have tried (B18) returns 0.

[StephenR]

Please see a couple of different approaches in the attached file.

[inky]

thanks for the suggestions Stephen, yet i can't use a SUMPRODUCT nor a pivot table. there are 1600 account lines and 250 columns, and i simply can't define each. this formula needs to be used across 10-15 worksheets all referencing the same data source.

i've seen this lookup done with a SUMIF and dynamic ranges/criteria yet i can't seem to understand the process and the SUMIF doesn't resolve neatly like a SUMPRODUCT does.

please don't think i'm ungrateful for your suggestions or contribution, more frustrated by excel!

[NBVC]

Does this work for you?

=SUMIF($A$2:$A$10,$A14,INDEX($A$1:$F$10,2,MATCH(B$13,$A$1:$F$1,0)):INDEX($A$1:$F$10,10,MATCH(B$13,$A$1:$F$1,0)))

copied down and across.

[inky]

it does! thanks NBVC, you're a handy smurf!!

can i ask how the INDEX/MATCHs comprise the SUMIF range,criteria and sum_range format? that's the bit i don't understand as the formula doesn't resolve nicely.

[inky]

oh, and another question if it's ok, in the INDEX i notice you're detailed the first and last rows of the range "2" and "10".

what if the last row of the range was unknown? could you include an OFFSET function to determine the "10" in the second INDEX function and use a named dynamic range for "A2:A10" etc?