Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Solution?
Hi There,
A B A B B A A B A .... A4251 A4231 A4261 Data numbers A4272 A4278 AUnique .... I would like to sum all data for which criteria in first row = A & Code in first column equals eg A4261 Explained differently: summing all data which is on line A4261, condition that there is an A in the first row. Any help really appreciated, Sige |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Solution?
=SUMIF($B$1:$AZ$1,"A",$B2:$AZ2)
etc. -- HTH RP (remove nothere from the email address if mailing direct) "Sige" wrote in message oups.com... Hi There, A B A B B A A B A .... A4251 A4231 A4261 Data numbers A4272 A4278 AUnique ... I would like to sum all data for which criteria in first row = A & Code in first column equals eg A4261 Explained differently: summing all data which is on line A4261, condition that there is an A in the first row. Any help really appreciated, Sige |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Solution?
If your data is in Cells A1:G10, try this:
=SUMPRODUCT((A2:A10="A4261")*(B1:G1="A")*B2:G10) Adjust the range references to suit your situation. Does that help? *********** Regards, Ron "Sige" wrote: Hi There, A B A B B A A B A .... A4251 A4231 A4261 Data numbers A4272 A4278 AUnique .... I would like to sum all data for which criteria in first row = A & Code in first column equals eg A4261 Explained differently: summing all data which is on line A4261, condition that there is an A in the first row. Any help really appreciated, Sige |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Solution?
Hi Ron,
It is exactly what it needed...! Thx Bob, I read your excellent paper about Sumproduct ..and Names. I have a question about the latter too: Quote If the 'Data Validation' cell is on a different worksheet from the list range, you will get an error if you try to use a range reference, even if preceded by the worksheet name. The way to overcome this is to define a Name for that list range, and use that in the 'Data Validation' list Source editbox. <Unquote If I define a Name and would like to use it as a List-range for the datavalidation ... I get the Defined Name in the list but not the items defined in my Range So name "SIGE" = B2:B10 Will show me "SIGE" in my list, instead of the items in the defined range. Brgds Sige |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Solution?
Sige,
You have to use = before the name, such as =SIGE, not just the range name. -- HTH RP (remove nothere from the email address if mailing direct) "Sige" wrote in message oups.com... Hi Ron, It is exactly what it needed...! Thx Bob, I read your excellent paper about Sumproduct ..and Names. I have a question about the latter too: Quote If the 'Data Validation' cell is on a different worksheet from the list range, you will get an error if you try to use a range reference, even if preceded by the worksheet name. The way to overcome this is to define a Name for that list range, and use that in the 'Data Validation' list Source editbox. <Unquote If I define a Name and would like to use it as a List-range for the datavalidation ... I get the Defined Name in the list but not the items defined in my Range So name "SIGE" = B2:B10 Will show me "SIGE" in my list, instead of the items in the defined range. Brgds Sige |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Solution?
Blushing like a Purbeck...
Thanks Bob Bob Phillips wrote: Sige, You have to use = before the name, such as =SIGE, not just the range name. -- HTH RP (remove nothere from the email address if mailing direct) "Sige" wrote in message oups.com... Hi Ron, It is exactly what it needed...! Thx Bob, I read your excellent paper about Sumproduct ..and Names. I have a question about the latter too: Quote If the 'Data Validation' cell is on a different worksheet from the list range, you will get an error if you try to use a range reference, even if preceded by the worksheet name. The way to overcome this is to define a Name for that list range, and use that in the 'Data Validation' list Source editbox. <Unquote If I define a Name and would like to use it as a List-range for the datavalidation ... I get the Defined Name in the list but not the items defined in my Range So name "SIGE" = B2:B10 Will show me "SIGE" in my list, instead of the items in the defined range. Brgds Sige |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
Generating excel combinations | Excel Discussion (Misc queries) | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |