Sum If Non Blank

[Lindsey]

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

 

[joel]

=sumproduct(--(A1:A100<>""),B1:B100)

 

[Gary''s Student]

=SUMPRODUCT((A1:A1000<>"")*(B1:B1000))

SUMPRODUCT is really powerful. See:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Lindsey]

Is it giving me a #NAME?, the columns are on another sheet.

=SUMPRODUCT(--(REGION_PORT_DETAIL!D2500000<>""),REGION_PORT_DETAIL!L2:L500000)

 

[Gary''s Student]

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

 

[Lindsey]

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]

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.