Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum data with criteria stored in row and column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum data with criteria stored in row and column
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 "vivi" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum data with criteria stored in row and column
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. -- John C "John C" wrote: 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 "vivi" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum data with criteria stored in row and column
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? "John C" wrote: 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. -- John C "John C" wrote: 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 "vivi" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting data that matches certain criteria in one column | Excel Discussion (Misc queries) | |||
Count data in one column if certain criteria exists in another. | Excel Worksheet Functions | |||
Help getting first column data based on criteria? | Excel Worksheet Functions | |||
Help with Grouping the numeric data stored in one column. | Excel Worksheet Functions | |||
How to add data to a column based on criteria from another | Excel Discussion (Misc queries) |