Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct not working
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct not working
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct formulas not working after editing | Excel Worksheet Functions | |||
sumproduct w/horizontal range not working | Excel Worksheet Functions | |||
Sumproduct suddenly not working | Excel Discussion (Misc queries) | |||
Sumproduct w/date criteria not working | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions |