Sum if there is "something" in a cell?

[notsurehowtodothis]

I have two columns of information, names in column A and the numbers associated with those names in column B. Some of the cells in column A are blank spaces but have numbers associated, I do not want to sum these numbers, I only want to sum the numbers that have names in column A. I know there is a way to do this, but I just can't figure it out. Thanks!

 

[MrKowz]

Try:

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

 

[notsurehowtodothis]

That works perfectly, thanks. What does the -- do?

 

[GreenyMcDuff]

I believe the -- is a double negative. So essentially the code is saying If there is not nothing (i.e. if there is something) in these cells then sum them.

Hope this helps

 

[MrKowz]

The -- coerces the conditional statement within the parenthases into a 1 or a 0. By doing this, it creates an array of 1s and 0s. This is useful in SUMPRODUCT because it takes each item in the arrays and multiplies them together, then adds them.

Lets take this data for example:

Excel Workbook
	A	B
1	foo	1
2	stuff	2
3	text	4
4	here	5
5		2
6	jack
7		9
8	snacks	3
Sheet1

Excel 2003



When we use =SUMPRODUCT(--(A1:A8<>""),B1:B8), the -- makes sumproduct instead look at it in this fashion:

Excel Workbook
	A	B
1	1	1
2	1	2
3	1	4
4	1	5
5	0	2
6	1
7	0	9
8	1	3
Sheet1

Excel 2003



Then, following the SUMPRODUCT logic, A1*B1+A2*B2+A3*B3+etc... it performs the following:

(1*1)+(1*2)+(1*4)+(1*5)+(0*2)+(1*0)+(0*9)+(1*3) = 15

This can also be done on more than two dimensions, which makes it useful for doing a multi-conditional SUMIF or even used just to simply count over multiple conditions.

Hope that helps! If you would like any further explanation or more examples, lemme know!

 

[GreenyMcDuff]

I guess I was wrong but that is good to know.

 

[Jonmo1]

The sumproduct isn't really needed here.
Try Sumif

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

 

[MrKowz]

The sumproduct isn't really needed here.
Try Sumif

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

Hmm... I must have had a typo when I tried doing that... gave me 0 earlier.

This would be the optimal way to do it, as SUMPRODUCT is an array formula and does tend to be sluggish, ESPECIALLY when looking at a lot of data.

 

[notsurehowtodothis]

Man, you guys are a wealth of information. I appreciate the guidance and I've used the sumproduct now because I only have about 100 rows of data, but sumif goes into my memory bank for future uses.

Thanks again fellas!