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!!!

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!!!

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!!!

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!!!

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!!!

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!!!

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!!!