sumproduct not working
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.
--
Boris
"Bob Phillips" wrote:
SUMPRODUCT doesn't work with complete columns, you have to specify a range.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"BorisS" wrote in message
...
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)),--(Actua
l!$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.
--
Boris
|