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

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


Joined: 05 May 2012
Posts: 7

PostPosted: Sat May 05, 2012 2:40 am    Post subject: =SUMPRODUCT Reply with quote

I've just started adding some data from a survey that I have created, I've been using the =SUMPRODUCT function which does the job.

However I have become stumped as the next survey question is a checkbox answer(multiple answers)

Is there a way to enter a numeric equivalent of what they have checked in a single cell (ie. 1,3,8,9) and get the =SUMPRODUCT function to add up a specific number?

Hope this makes sense?
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat May 05, 2012 3:45 am    Post subject: Re: =SUMPRODUCT Reply with quote

NiceGuy88 wrote:
Hope this makes sense?
Not really.
Is it possible you are referring to the fact that the checkbox results in text rather than number (as indicated by the leading apostrophe in the input line). If so then you could use the VALUE function in the SUMPRODUCT function [=SUMPRODUCT(VALUE(A1:A5)=3)]or just check for a text value [=SUMPRODUCT(A1:A5="3")]

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
NiceGuy88
General User
General User


Joined: 05 May 2012
Posts: 7

PostPosted: Sat May 05, 2012 3:55 am    Post subject: re: =SUBPRODUCT Reply with quote

I've been using the current formula as you suggest:

=SUMPRODUCT(A1:A5=3)

But only works when there is ONLY a '3' in the cell?

how do I seperate the numbers in a single cell to make it work? ive tried the following -

1,2,3,4,5
1:2:3:4:5
1;2;3;4;5
etc

it always comes back with a value of '0'
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat May 05, 2012 4:22 am    Post subject: Reply with quote

If A1 has 1,3,8,9 and you want to see if it has a 3 say, then, if all comma separated values are single digit, you could use =(LEN(A1)-LEN(SUBSTITUTE(A1;3;""))>0

If A1:A5 have comma separated single digit values you can count the cells with a 3 say using =SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5;3;"")))>0)

If comma separated values can be any number of digits then the second formula would need to be =SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(","&A1:A5&",";",3,";"")))>0)

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
NiceGuy88
General User
General User


Joined: 05 May 2012
Posts: 7

PostPosted: Sat May 05, 2012 4:29 am    Post subject: [Solved] =SUMPRODUCT Reply with quote

Thanks, that's perfect Smile
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