# Excel 2007 : Assigning values in cells and then summing total score excluding blanks

1. ## Assigning values in cells and then summing total score excluding blanks

Hi All,
I've got a spreadhseet which has 9 non-contiguous variables which need to be 'scored' and then summed. For each of A, C, E, G, I, K, M, O, Q, responses can range from 1 to 4 (although some are missing) (see attached spreadsheet). I want to be able to score each response based on the following criteria:

if answer=1 or 2, then score = 1
if asnwer=3, then score = -1
if answer=4, then score=0
if answer is missing, then score= MISSING

Using this scoring protocol, scores can range from -9 to +9. As you will see in the atached spreadsheet, some cells are missing one or two scores and other rows are missing altogether.

Here is my problem. I want to be able to score each cell (see the 'ifs' above) and then sum across the non-contiguous columns to compute a final score WITHOUT counting the blanks as zeros. In other words, how can I add or SUM items up without Excel putting a darn 'zero' in the rows where there is no data?

Hope I've explained this okay and thanks in advance for your help.

2. ## Re: HELP Assigning values in cells and then summing total score excluding blanks

Welcome to the forum.

In A25, =SUMPRODUCT((A1:A23={1,2,3,4}) * {1,1,-1,0})

3. ## Re: Assigning values in cells and then summing total score excluding blanks

Originally Posted by shg
Welcome to the forum.

In A25, =SUMPRODUCT((A1:A23={1,2,3,4}) * {1,1,-1,0})
hi, sorry should have been clearer, the scoring is supposed to take place across rows not columns, so the A1:A23 will not work, I need something which assigns and then adds up A1, A3, A5, etc.

4. ## Re: Assigning values in cells and then summing total score excluding blanks

i tried the following to no avail, any ideas? is what i am trying to do possible??

=SUMPRODUCT((D2,G2,J2,M2,P2,S2,V2,Y2,AB2={1,2,3,4}) * {1,1,-1,0})

5. ## Re: Assigning values in cells and then summing total score excluding blanks

In S1: =SUMPRODUCT((A1:Q1={1;2;3;4}) * {1;1;-1;0})

6. ## Re: Assigning values in cells and then summing total score excluding blanks

I've just realised what this means, after ages spent going down the wrong road!
.....without Excel putting a darn 'zero' in the rows where there is no data
Original posted content removed.

Using shgs' Formula in S1
``Please Login or Register  to view this content.``

And in R1 (to count missing entries)
``Please Login or Register  to view this content.``

7. ## Re: Assigning values in cells and then summing total score excluding blanks

Originally Posted by shg
In S1: =SUMPRODUCT((A1:Q1={1;2;3;4}) * {1;1;-1;0})
Hi shg,
Thanks again fro your prompy and help ful reply. The cells in B1, E1, G1 etc. have values in them so putting in the range A1:Q1 will score these cells as well. Can SUMPRODCUT be used with cells which are not next to each other, ie. non-contiguous? It would saveme having to copy the workseet to a new one and deleting out the columns of data which are causing the problems.

Cheers.

8. ## Re: Assigning values in cells and then summing total score excluding blanks

Originally Posted by Marcol
I've just realised what this means, after ages spent going down the wrong road!

Original posted content removed.

Using shgs' Formula in S1
``Please Login or Register  to view this content.``

And in R1 (to count missing entries)
``Please Login or Register  to view this content.``
Hi Marcol,
Thanks to you ahd shg for very helpful replies. We are getting close but the columns in between a1 and c1 have data in them but are not supposed to be part of the scoring formula- here is a new spreadsheet which gives a fuller picture. As you will see the range in SUMPRODUCT will pick up on these and not give the correct score at the end of the row. What I did was copy the worksheet into a new one and remove the columns which are causing problems and the revised formula both you and shg sent are great, but I was wondering whether this was necessary. In other words, can SUMPRODUCT be used with non-contigious cells? b2, d2, f2, etc. while leaving out c2, e2, g2, etc.?? [please see spreadsh\eet for futher info]

I really do appreciate the time you and shg have taken to answer my query- this problem has racked my head for the last day or so.

Cheers

9. ## Re: Assigning values in cells and then summing total score excluding blanks

I can't see an easy formula for this problem, so I have written a couple of UDFs. (I can hear the boos and hisses already)....
``Please Login or Register  to view this content.``
Entered thus
``Please Login or Register  to view this content.``

The Incremental step is optional the default is 2, if you need a different step then enter the function so
``Please Login or Register  to view this content.``

Hope this helps.

10. ## Re: Assigning values in cells and then summing total score excluding blanks

Marcol,
THANKS EVER SO MUCH! I can only imagine how long it too you to do this, and it is much appreciated. I will have a go at gettin this to work. All the very best!!

Originally Posted by Marcol
I can't see an easy formula for this problem, so I have written a couple of UDFs. (I can hear the boos and hisses already)....
``Please Login or Register  to view this content.``
Entered thus
``Please Login or Register  to view this content.``

The Incremental step is optional the default is 2, if you need a different step then enter the function so
``Please Login or Register  to view this content.``

Hope this helps.

11. ## Re: Assigning values in cells and then summing total score excluding blanks

PLEASE don't quote whole posts. Use the REPLY button, not the QUOTE button.

Thanks.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1