sumproduct not working

[Guest]

I have one sheet which has names of expenses on the left column (A), and one
date on the top of each of 12 columns (the first one, for the example below,
is C, and the date is specifically in C1). I have another sheet (named
"actual")where I am entering a list of expenses with the date (column A), the
type (column B, and which names match the left column A on the sheet for this
function), and the amount (column C).

I need each cell in the first sheet to go to the second, and if both the
month of the expense and the type match the month and type intersection of
the cell in which I am typing the formula, I need it to sum up all of the
amounts. I think I need sumproduct, given the multiple criteria, but the
following is giving me a NUM!:

=SUMPRODUCT(--(Actual!$B:$B=$A4),--(MONTH(Actual!$A:$A)=MONTH(C$1)),--(Actual!$C:$C))

So the above's intent is to say "if the value of an item in column B on
'actual', where column B is the type, matches the type that is listed in cell
A4 AND if the month of the date in the corresponding 'actual' entry in column
A matches the month of C1, which is the month I'm trying to sum up, then add
that to the running sum of amounts that I want summed in this cell."

Hope that makes sense, and hope someone can advise where I'm going wrong.

Thx.

 

[Bob Phillips]

SUMPRODUCT doesn't work with complete columns, you have to specify a range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

thanks kindly. you hit it on the head. went with an offset solution for a
named range, which lets me not be strict about size dimensions, but still
works in the sumproduct.

thanks again.

 

[Aladin Akyurek]

Formulas that operate on computed arrays does not admit whole columns as
reference. Try definite ranges:

=SUMPRODUCT(--(Actual!$B$2:$B$400=$A4),
--(Actual!$A$2:$A$400-DAY(Actual!$A$2:$A$400)+1=C$1),
Actual!$C$2:$C$400)

Make sure that C1 houses the first day date of a month/year of interest
like 1-Jan-2005. Take note of 1.

PS. If you are on Excel 2003, convert the data area on Actual including
the headers into a list by means of Data|List|Create List and enjoy the
benefits of the List functionality.