OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

SUMPRODUCT recognizing zeros vs. empty cells

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
alanack
General User
General User


Joined: 01 Jan 2008
Posts: 25

PostPosted: Tue May 20, 2008 11:35 am    Post subject: SUMPRODUCT recognizing zeros vs. empty cells Reply with 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. 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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 20, 2008 11:40 am    Post subject: Reply with quote

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
View user's profile Send private message
alanack
General User
General User


Joined: 01 Jan 2008
Posts: 25

PostPosted: Tue May 20, 2008 12:13 pm    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 20, 2008 12:20 pm    Post subject: Reply with quote

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
View user's profile Send private message
alanack
General User
General User


Joined: 01 Jan 2008
Posts: 25

PostPosted: Tue May 20, 2008 12:34 pm    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 20, 2008 12:45 pm    Post subject: Reply with quote

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
View user's profile Send private message
alanack
General User
General User


Joined: 01 Jan 2008
Posts: 25

PostPosted: Tue May 20, 2008 1:13 pm    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 20, 2008 1:20 pm    Post subject: Reply with quote

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.
Re-reading 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
View user's profile Send private message
alanack
General User
General User


Joined: 01 Jan 2008
Posts: 25

PostPosted: Wed May 21, 2008 5:57 am    Post subject: Reply with quote

This formula does the trick, thanks, Villeroy, for all your help on this.
Back to top
View user's profile Send private message
alanack
General User
General User


Joined: 01 Jan 2008
Posts: 25

PostPosted: Wed May 21, 2008 11:03 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed May 21, 2008 11:19 am    Post subject: Reply with quote

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
View user's profile Send private message
alanack
General User
General User


Joined: 01 Jan 2008
Posts: 25

PostPosted: Wed May 21, 2008 11:56 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed May 21, 2008 12:24 pm    Post subject: Reply with quote

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
View user's profile Send private message
alanack
General User
General User


Joined: 01 Jan 2008
Posts: 25

PostPosted: Wed May 21, 2008 1:58 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group