Sum data by column and row "NAME"

[HadouKevin]

(MS Excel 2010)

I have reports that fluctuate column and row data location. Today, the column of "TOY01" was in [P] , the day before when I received the report, the column of "TOY01" was in [S]. The corresponding row to the column is also changing, in most cases multiple rows as well. Example:

	A	B	C	D
1		TOY01	TOY02	TOY03
2	TOM	1	0	0
3	BILL	0	1	0
2	TOM	1	0	0
3	BILL	0	1	0

I'd like is to see that Tom sold 2 "TOY01", and Bill sold 2 "TOY02", knowing that the different columns for the different "TOY" types will will most likely be different on the next report. And one last detail, pivots are not an option, let me know if any more details are needed. Thanks!

[martindwilson]

you could try like this using
offset and match for example
=SUMIF(rawdata!$A$2:$A$1000,$A2,OFFSET(rawdata!$A$1,1,MATCH(B$1,rawdata!$B$1:$AA$1,0),COUNTA(rawdata!$A$1:$A$1000)))

[FDibbins]

dang Martin, and to think that 1 day, worms may be eating a brain like that

[HadouKevin]

Wow; going to need some time to absorb this....thank you for the response. Looks great!

[HadouKevin]

This works as requested, thanks again!

I am however very interested in the "why" this works If you don't mind. The "OFFSET" is very much giving me wrinkles.

[martindwilson]

offset can be used to create a range at its simplest
=OFFSET(A1,1,1) is equal to b2 ie 1 cell down(row) one across(column) from A1
but there are options to make it a range by increasing the number of rows or columns
=OFFSET(A1,1,1,10)
refers to the range b2:b11 ie start 1 row down and one column across from a1 ie b2 and include 10 rows
=OFFSET(A1,1,1,,10) does the same but starts at b2 and goes across 10 columns
creating b2:k2
then you can include a whole range by specifying rows and columns
eg =OFFSET(A1,1,1,10,5)
= b2:f5
when used as a range on its own it shows #VALUE!
in the cell
but can be used in other calculations like
=sum(OFFSET(A1,1,1,10,5)) that will ad up every thing in b2:f5
the COUNTA(rawdata!$A$1:$A$1000) bit i included just counts how many cells in the column a1:a1000 are used to make the number of rows dynamic you could just put 100 or 1000 or however many you are likely to have