View previous topic :: View next topic 
Author 
Message 
alanack General User
Joined: 01 Jan 2008 Posts: 25

Posted: Tue May 20, 2008 11:35 am Post subject: SUMPRODUCT recognizing zeros vs. empty cells 


I'm using the formula =SUMPRODUCT(A1:A100*B1:B100) to sum the instances in column B where there is a value in column A. Unfortunately, if there is a zero in column A it treats it as if it were blank, and doesn't add the corresponding value in column B. Is there a way to get the formula to count the instances where there is a zero in column A(get it to distinguish between a zero and an empty cell)? Thanks.
Alan 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Tue May 20, 2008 11:40 am Post subject: 


Code:  =SUMPRODUCT(A1:A100*B1:B100;ISNUMBER(A1:A100);ISNUMBER(B1:B100))  [/code] _________________ Rest in peace, oooforum.org
Get help on https://forum.openoffice.org 

Back to top 


alanack General User
Joined: 01 Jan 2008 Posts: 25

Posted: Tue May 20, 2008 12:13 pm Post subject: 


Thanks. But I plugged this in, and could not get it to work(going over it several times to make sure it was entered exactly as shown). I'm getting a 508 error code(bracket missing), but can't quite figure it out. Any idea what might be going on? Thanks. 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Tue May 20, 2008 12:20 pm Post subject: 


I put values with some gaps in A1:B100, paste the formula somewhere on the same sheet and it works. _________________ Rest in peace, oooforum.org
Get help on https://forum.openoffice.org 

Back to top 


alanack General User
Joined: 01 Jan 2008 Posts: 25

Posted: Tue May 20, 2008 12:34 pm Post subject: 


Thanks, Villeroy. But I pasted the formula in to my spreadsheet, and no B values were summed. I replaced the two zeros in the A column with 1s, and it it summed their corresponding cells in the B column. I'm using version 2.4.0. 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Tue May 20, 2008 12:45 pm Post subject: 


It sums all products A*B where both A and B are numbers. _________________ Rest in peace, oooforum.org
Get help on https://forum.openoffice.org 

Back to top 


alanack General User
Joined: 01 Jan 2008 Posts: 25

Posted: Tue May 20, 2008 1:13 pm Post subject: 


Yes, there is no text whatsoever. It seems a bit of a mystery, perhaps I should reinstall openoffice. Is there another approach to solving this problem without using the ISNUMBER function? 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Tue May 20, 2008 1:20 pm Post subject: 


alanack wrote:  Yes, there is no text whatsoever. It seems a bit of a mystery, perhaps I should reinstall openoffice. Is there another approach to solving this problem without using the ISNUMBER function? 
You are using Windows, aren't you?
No need to delete and install. OOo does exactly what the formula is supposed to do.
Rereading your initial purpose:
Quote:  I'm using the formula =SUMPRODUCT(A1:A100*B1:B100) to sum the instances in column B where there is a value in column A 
I come up with this one:
Code: 
=SUMPRODUCT(B1:B100;ISNUMBER(A1:A100))

_________________ Rest in peace, oooforum.org
Get help on https://forum.openoffice.org 

Back to top 


alanack General User
Joined: 01 Jan 2008 Posts: 25

Posted: Wed May 21, 2008 5:57 am Post subject: 


This formula does the trick, thanks, Villeroy, for all your help on this. 

Back to top 


alanack General User
Joined: 01 Jan 2008 Posts: 25

Posted: Wed May 21, 2008 11:03 am Post subject: 


The formula above does work. However, the formula I'm using is a bit more complicated. I need to specify a condition in the first column. The formula =SUMPRODUCT(A1:A100;ISNUMBER(B1:B100)) does sum the values when there is a zero in the a column, and ignores empty cells, as you suggested it would. But when I try to add a condition to the A column it sums all values in the B column for some reason. What I tried was:
=SUMPRODUCT(B1:B100;ISNUMBER(A1:A100=C10)) and
=SUMPRODUCT(B1:B100;ISNUMBER(A1:A100="C10"))
Any ideas how I might add this condition? Thanks. 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Wed May 21, 2008 11:19 am Post subject: 


Get a free column let's say at X1.
Set array formula:
X1: =ISNUMBER(A1:A100) [Ctrl+Shift+Enter]
Y1: =A1:A100="C10" [Ctrl+Shift+Enter]
Z1: =ISNUMBER(A1:A100="C10") [Ctrl+Shift+Enter]
Z returns TRUE for all values.
=A1="C10" asks if cell A1 has a literal string value "C10", which it does not have, thus the result is 0 (zero formatted as boolean is shown as FALSE).
Now you ask if the comparision A1="C10" yields a number, which returns 1 in any case (one formatted as boolean is shown as TRUE). 0 and 1 (TRUE and FALSE) is always a number, with any comparison. _________________ Rest in peace, oooforum.org
Get help on https://forum.openoffice.org 

Back to top 


alanack General User
Joined: 01 Jan 2008 Posts: 25

Posted: Wed May 21, 2008 11:56 am Post subject: 


Thanks, Villeroy. I was able to create the columns you suggested, but at this point I get hung up(you should probably know that I have no programming experience). It seems to me the problem is it is treating all empty cells as zeros. Is there a simple solution somewhere here by somehow simply excluding blank cells, or using cells only if they contain a value? Perhaps using "<>" as one of the conditions? Wouldn't this work then when there was a zero in the column and a zero in the C10 cell? Thanks again for all your help. 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Wed May 21, 2008 12:24 pm Post subject: 


I have no idea what you're after. You have values in A1:A100, B1:B100 and some cell C10.
So far, we have a formula which sums all B where A has a number:
Code:  =SUMPRODUCT(B1:B100;ISNUMBER(A1:A100)) 
Now, what is C10 supposed to do?
This is how SUMPRODUCT(A1:A5;ISODD(A1:A5)) works:
First column has numbers, second column starts with =ISODD(A1) and returns 1(true) for odd numbers in A and 0 (false) for even numbers in A:
Code: 
1 1
2 0
3 1
4 0
5 1

Get the product for each row:
Code: 
1 * 1 =1
2 * 0 =0
3 * 1 =3
4 * 0 =0
5 * 1 =5

The sum of the products is 9. _________________ Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Last edited by Villeroy on Wed May 21, 2008 2:09 pm; edited 1 time in total 

Back to top 


alanack General User
Joined: 01 Jan 2008 Posts: 25

Posted: Wed May 21, 2008 1:58 pm Post subject: 


Sorry I didn't make myself more clear. I just wanted to add a condition to the formula you suggested above, which, in its simple form, did just what I wanted it to do. C10 is just a number generated from the data which I want to use as part of a condition in this case  i.e., sum the numbers in column B in the same row as those of column A wherein 1) the cell is not blank 2) the value is equal to the value in cell C10. It all works fine except when the value of C10 is zero. Then it treats the blank cells as if they contain a zero also and sums the entire column C. I ran across this use of SUMPRODUCT to sum numbers in a column with multiple conditions in an openoffice Wiki article, and this formula solves the problem:
=SUMPRODUCT(A1:A6="red";B1:B6="big";C1:C6)
Of course, it sums the values in column C where both red in A and big in B are met. My formula looks like this, the second condition being that the cell not be blank:
=SUMPRODUCT(A1:A100=C10;A1:A100<>"";B1:B100)
Thanks for all your effort, I appreciate it.
Alan 

Back to top 


