SUMIF and lookup function to combine data

[inky]

these SUMIFs stiil are causing me some issues :o(

i've attached a spreadsheet showing my attempt at a formula and at what i'd like to achieve. what that is is, take data that is found on "sheet 1" at "P" level, extract it and ignore duplicate "acct" lines, and accumulate it into "groups" as contained in a table (hence the INDEX/MATCH lookup without absolute cell reference).

some comments:
-data is 250 columns wide and 300-400 rows so can't use SUMPRODUCT as i can't physically define every range on "sheet1".
- the relationships may change hence the table on "sheet3".
- the data can not be aggregated before it is copied to "sheet1" yet must be aggregated when displayed on "sheet2".

thanks in advance - i think i'm partly there yet also think i have a long way to go.

[snasui]

Try this,

B14 on sheet2

Please Login or Register  to view this content.

Ctrl+Shift+Enter copy across then down.

See attached file.

Hope this helps

[inky]

thanks snasui, that works great! but how?!?!

i've evaluated the formula and can not follow the following:
1. SUMIF in each column.
2. where the lookup column is used to extract the P's to their groups. i think it's the OFFSET and ROW combination but not sure.

could you please explain these components so i can understand the formula?

[snasui]

From Private Message...

Sorry for my late, My business is very busy. I hope this attached file can solve you problem.