sumproduct not working

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.

--
Boris

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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.

BorisS wrote:
> 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.
>