Excel 2007 : 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.

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.

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})

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.

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

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.