Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with varying # of rows
Hi,
I a few sets of data that would have varying rows, and I need to get the summation of products of cullumn B*C. How should I do this in excel? using sumproduct(B:B,C:C) gives me a num error. I want to be able to have this dynamically updated, hence, I don't want to do this in a macro(unless a macro can be automatically executed). Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with varying # of rows
Ren
I think the problem you are having is because SUMPRODUCT() does not work with an entire column. Try =SUMPRODUCT (B1:B65535,C1:C65535) or adjust to maximum number of rows you need. Dave "Ren" wrote in message ... Hi, I a few sets of data that would have varying rows, and I need to get the summation of products of cullumn B*C. How should I do this in excel? using sumproduct(B:B,C:C) gives me a num error. I want to be able to have this dynamically updated, hence, I don't want to do this in a macro(unless a macro can be automatically executed). Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with varying # of rows
I don't understand!
If you're willing to use B:B and C:C, why would you want/need a dynamic range. With Sumproduct and all array formulas, entire column references are not allowed (XL07 will change this), so use: B1:B65535 which is *1* cell short of the entire column. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Ren" wrote in message ... Hi, I a few sets of data that would have varying rows, and I need to get the summation of products of cullumn B*C. How should I do this in excel? using sumproduct(B:B,C:C) gives me a num error. I want to be able to have this dynamically updated, hence, I don't want to do this in a macro(unless a macro can be automatically executed). Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with varying # of rows
Brilliant. You are right. Now it works, and I thought it was because
SUMPRODUCT() doesn't work with empty cells. Thanks "Dave" wrote: Ren I think the problem you are having is because SUMPRODUCT() does not work with an entire column. Try =SUMPRODUCT (B1:B65535,C1:C65535) or adjust to maximum number of rows you need. Dave "Ren" wrote in message ... Hi, I a few sets of data that would have varying rows, and I need to get the summation of products of cullumn B*C. How should I do this in excel? using sumproduct(B:B,C:C) gives me a num error. I want to be able to have this dynamically updated, hence, I don't want to do this in a macro(unless a macro can be automatically executed). Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with varying # of rows
Sorry. I didn't know that colum references are not allowed with array
formulas, and assumed that it had something to do with the way emtpy cells are handled by sumproduct(), which is why then thought about doing a dynamic range. Yes, changing it to B1: B65000 does solve my problem. Thanks "RagDyeR" wrote: I don't understand! If you're willing to use B:B and C:C, why would you want/need a dynamic range. With Sumproduct and all array formulas, entire column references are not allowed (XL07 will change this), so use: B1:B65535 which is *1* cell short of the entire column. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Ren" wrote in message ... Hi, I a few sets of data that would have varying rows, and I need to get the summation of products of cullumn B*C. How should I do this in excel? using sumproduct(B:B,C:C) gives me a num error. I want to be able to have this dynamically updated, hence, I don't want to do this in a macro(unless a macro can be automatically executed). Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with varying # of rows
Appreciate for the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ren" wrote in message ... Sorry. I didn't know that colum references are not allowed with array formulas, and assumed that it had something to do with the way emtpy cells are handled by sumproduct(), which is why then thought about doing a dynamic range. Yes, changing it to B1: B65000 does solve my problem. Thanks "RagDyeR" wrote: I don't understand! If you're willing to use B:B and C:C, why would you want/need a dynamic range. With Sumproduct and all array formulas, entire column references are not allowed (XL07 will change this), so use: B1:B65535 which is *1* cell short of the entire column. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Ren" wrote in message ... Hi, I a few sets of data that would have varying rows, and I need to get the summation of products of cullumn B*C. How should I do this in excel? using sumproduct(B:B,C:C) gives me a num error. I want to be able to have this dynamically updated, hence, I don't want to do this in a macro(unless a macro can be automatically executed). Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto expand rows | Excel Worksheet Functions | |||
Rows & Columns in Excel | Excel Worksheet Functions | |||
Automatically inserting rows | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
flexible paste rows function that inserts the right number of rows | Excel Discussion (Misc queries) |