[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
NiceGuy88
General User

Joined: 05 May 2012
Posts: 7

 Posted: Sat May 05, 2012 2:40 am    Post subject: =SUMPRODUCT 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?
ken johnson
Super User

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

Posted: Sat May 05, 2012 3:45 am    Post subject: Re: =SUMPRODUCT

 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
_________________
NiceGuy88
General User

Joined: 05 May 2012
Posts: 7

 Posted: Sat May 05, 2012 3:55 am    Post subject: re: =SUBPRODUCT 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'
ken johnson
Super User

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

 Posted: Sat May 05, 2012 4:22 am    Post subject: 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).
NiceGuy88
General User

Joined: 05 May 2012
Posts: 7

 Posted: Sat May 05, 2012 4:29 am    Post subject: [Solved] =SUMPRODUCT Thanks, that's perfect
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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