Sum data with criteria stored in row and column

[vivi]

Hi there

I hope someone can help me. I try to use sumproduct to sum up the data on my
data sheet as follow:

=SUMPRODUCT(--(A6='Actual FTE(£)'!A8:A80),--('Phased Costs By
Deparments'!B4='Actual FTE(£)'!M6:AV6),('Actual FTE(£)'!M8:AV80))

However, it doesn't work!!! and I am not sure why...

Basically, I have a data sheet which contains Jan'08-Dec'10 as the column
header, then on the side, the row headers are the functions for my company:

Function Resource Jan'08 Feb'08 Mar'08
BTS Allen 500 600 650
BTS Matthew 200 700 900
Finance Lisa 1000 900 700
Actuarial Vivian 150 450 650
Legal Wilson 250 100 150
Complian Darren 300 560 500
Complian Rosa 400 240 300


My report only show the monthly figures for a year, i.e. 12 columns.
Therefore when I sum up for each function, I also need to know which months
should be summed up for and calculate on my report.

I hope I explain myself clearly, if not please let me know...I think using
sumproduct is the solution but not sure why it is not working...please
someone help!!!!

Thansk a lot in advance

Vivi

 

[John C]

SUMPRODUCT needs identically sized ranges, and ranges that are related to
each other
Your first and third ranges are identically sized and in the same direction,
but your middle range is smaller, and 90 degrees out of phase with the other
2 ranges.

 

[John C]

Oh, and your third range has to be a single range as well (i.e.: not M8:AV80,
but M8:M80, etc). Perhaps you could have one more column that is a total
column, and use that for the 3rd range.

 

[CAR]

I have the same issue as vivi and was trying to use sumproduct.
I understand your explanation as to why the formula is not working, but I am
looking for a solution to the problem - I have criteria to be matched along
column A and criteria to be matched along row 3, and I'd like to return the
value at the intersection of column A and row 3 if the criteria are both met.
Any ideas?