SUMIF with dynamic criteria

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.

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

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!

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.

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?

No problem, I should have read your question properly. Looks like you're sorted now but not completely sure why SUMPRODUCT wouldn't work.

thanks NBVC. i'll try that today.

NBVC, i just wanted to say thanks for pointing me in the right direction with your formula.

since i could follow your suggestion, and it seemed to resolve better than some other SUMIF solutions i've seen on the net i was able to figure out how to apply it to my actual situation.

happy new year everyone!

i guess 2008 continues to bring us all Excel questions...

i've discovered an issue in the attached formula and can't seem to resolve it. to refresh, what needs to occur is as follows. data is contained in the range A1:G12. this needs to be summarised into groups as suggested by range A15:G18. not all "Ps" will be present in the data range, yet all Ps are mapped to their higher level Group in range A22:B28.

yet in the attached suggestion i can not see how the formula refers to the headings in Row 1? Also, there are no "P" items allocated to Group3 yet there are summary results. similarly Group4 should equal the P4 items yet it equals the P7 items.

can anyone help?