| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum If Non Blank
I have a long list of names in column A, with blank cells every so often. In
column B, there are list of numbers. Cake 8 Cookies 4 3 Cake 5 9 Cookies 2 I want to a sum from column B if the cell in column A is not blank. Please let me know if you have any other questions, thanks!!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum If Non Blank
=sumproduct(--(A1:A100<""),B1:B100) "Lindsey" wrote: I have a long list of names in column A, with blank cells every so often. In column B, there are list of numbers. Cake 8 Cookies 4 3 Cake 5 9 Cookies 2 I want to a sum from column B if the cell in column A is not blank. Please let me know if you have any other questions, thanks!!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum If Non Blank
=SUMPRODUCT((A1:A1000<"")*(B1:B1000))
SUMPRODUCT is really powerful. See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200844 "Lindsey" wrote: I have a long list of names in column A, with blank cells every so often. In column B, there are list of numbers. Cake 8 Cookies 4 3 Cake 5 9 Cookies 2 I want to a sum from column B if the cell in column A is not blank. Please let me know if you have any other questions, thanks!!! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum If Non Blank
Is it giving me a #NAME?, the columns are on another sheet.
=SUMPRODUCT(--(REGION_PORT_DETAIL!D2:D500000<""),REGION_PORT_DE TAIL!L2:L500000) "Gary''s Student" wrote: =SUMPRODUCT((A1:A1000<"")*(B1:B1000)) SUMPRODUCT is really powerful. See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200844 "Lindsey" wrote: I have a long list of names in column A, with blank cells every so often. In column B, there are list of numbers. Cake 8 Cookies 4 3 Cake 5 9 Cookies 2 I want to a sum from column B if the cell in column A is not blank. Please let me know if you have any other questions, thanks!!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum If Non Blank
1. check the spelling of the tabname
2. make sure 500000 is really the number of rows (it seems REALLY big) 3. make sure you are using an English version and that SUMPRODUCT does not need to be re-spelled -- Gary''s Student - gsnu200844 "Lindsey" wrote: Is it giving me a #NAME?, the columns are on another sheet. =SUMPRODUCT(--(REGION_PORT_DETAIL!D2:D500000<""),REGION_PORT_DE TAIL!L2:L500000) "Gary''s Student" wrote: =SUMPRODUCT((A1:A1000<"")*(B1:B1000)) SUMPRODUCT is really powerful. See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200844 "Lindsey" wrote: I have a long list of names in column A, with blank cells every so often. In column B, there are list of numbers. Cake 8 Cookies 4 3 Cake 5 9 Cookies 2 I want to a sum from column B if the cell in column A is not blank. Please let me know if you have any other questions, thanks!!! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum If Non Blank
Yes, the spelling of the tab is right and I am using an English version. I
used 500000 because the list will be added to, so I want to make sure it included all cells. "Gary''s Student" wrote: 1. check the spelling of the tabname 2. make sure 500000 is really the number of rows (it seems REALLY big) 3. make sure you are using an English version and that SUMPRODUCT does not need to be re-spelled -- Gary''s Student - gsnu200844 "Lindsey" wrote: Is it giving me a #NAME?, the columns are on another sheet. =SUMPRODUCT(--(REGION_PORT_DETAIL!D2:D500000<""),REGION_PORT_DE TAIL!L2:L500000) "Gary''s Student" wrote: =SUMPRODUCT((A1:A1000<"")*(B1:B1000)) SUMPRODUCT is really powerful. See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200844 "Lindsey" wrote: I have a long list of names in column A, with blank cells every so often. In column B, there are list of numbers. Cake 8 Cookies 4 3 Cake 5 9 Cookies 2 I want to a sum from column B if the cell in column A is not blank. Please let me know if you have any other questions, thanks!!! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum If Non Blank
Remember that, in versions of Excel prior to 2007, there could only be a
maximum of 65536 rows. This means that if you entered: =SUMPRODUCT(A1:A100000) you would see: #NAME? If you are not using Excel 2007, you need to reduce that row indicator. -- Gary''s Student - gsnu200844 "Lindsey" wrote: Yes, the spelling of the tab is right and I am using an English version. I used 500000 because the list will be added to, so I want to make sure it included all cells. "Gary''s Student" wrote: 1. check the spelling of the tabname 2. make sure 500000 is really the number of rows (it seems REALLY big) 3. make sure you are using an English version and that SUMPRODUCT does not need to be re-spelled -- Gary''s Student - gsnu200844 "Lindsey" wrote: Is it giving me a #NAME?, the columns are on another sheet. =SUMPRODUCT(--(REGION_PORT_DETAIL!D2:D500000<""),REGION_PORT_DE TAIL!L2:L500000) "Gary''s Student" wrote: =SUMPRODUCT((A1:A1000<"")*(B1:B1000)) SUMPRODUCT is really powerful. See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200844 "Lindsey" wrote: I have a long list of names in column A, with blank cells every so often. In column B, there are list of numbers. Cake 8 Cookies 4 3 Cake 5 9 Cookies 2 I want to a sum from column B if the cell in column A is not blank. Please let me know if you have any other questions, thanks!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
| Nested IF - return a blank when compared cells are blank | New Users to Excel | |||
| How to return a blank formula cell if the reference is blank? | Excel Worksheet Functions | |||
| Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
| Not showing blank and non blank items in filter mode for values | Excel Worksheet Functions | |||
Linear Mode
