| View previous topic :: View next topic |
| 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? |
|
| Back to top |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
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' |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
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). |
|
| Back to top |
|
|
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  |
|
| Back to top |
|
|
|
