Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I want ot enter a formula in cell A20 which should be the sumproduct of range
A1 to A19 & of coloumn B1 to B19 but to exclude some cells from both coloumns, conditionality being if there are no figures in any of the cell (cell value being 0). Thus if cell A5=0, to ignore both cell A5 & B5. pL help. Thanks in anticipation |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Try
=SUMPRODUCT((A1:A19<0)*(A1:A19)*(B1:B19)) Mike "Harsh Bahal" wrote: I want ot enter a formula in cell A20 which should be the sumproduct of range A1 to A19 & of coloumn B1 to B19 but to exclude some cells from both coloumns, conditionality being if there are no figures in any of the cell (cell value being 0). Thus if cell A5=0, to ignore both cell A5 & B5. pL help. Thanks in anticipation |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Harsh,
if my recollection is correct, u can try this while u imagine ignoring the rows with 0's in it A20 =sumproduct(A1:A19,B1:B19) sidenote: Thus if cell A5=0, to ignore *both* cell A5 & B5. 0 x anynumber = 0 , thus the resulting SUM of the *valued* PRODUCTS from the pair of range (A1:B19) will be correct on A20. -- regards "Harsh Bahal" wrote: I want ot enter a formula in cell A20 which should be the sumproduct of range A1 to A19 & of coloumn B1 to B19 but to exclude some cells from both coloumns, conditionality being if there are no figures in any of the cell (cell value being 0). Thus if cell A5=0, to ignore both cell A5 & B5. pL help. Thanks in anticipation |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Continous number box | Excel Discussion (Misc queries) | |||
Sum of continous cell | Excel Worksheet Functions | |||
Print row labels that are continous | Excel Discussion (Misc queries) | |||
Continous updating of linked spreadsheet | Links and Linking in Excel | |||
countif with non-continous ranges | Excel Worksheet Functions |