# Thread: cell totals with blank spaces (Excel 2003)

1. ## cell totals with blank spaces (Excel 2003)

hi there, how can i total up the student number of questions answered (not the amountwhich is the score) just how many students answered question 1 etc, as their are blanks i need a function that will ignore blanks. also can i validate this table so it will enter a " - " sign where no number is inserted automatically, because on this table i am making it so you cant answer over 4 max questions. can you explain how to do this and why so i can understand please.

regards kitty

2. ## Re: cell totals with blank spaces (Excel 2003)

<P ID="edit" class=small>(Edited by Jezza on 10-Nov-07 20:24. Typed wrong function)</P>Hi

Simpleset way is to use SUMPRODUCT AND ISNUMBER

In cell G28 of your example type: =SUMPRODUCT(ISNUMBER(G10:G26)+0) and then drag across to cell J28

3. ## Re: cell totals with blank spaces (Excel 2003)

hi there jezza and thank you for your reply, but im still a beginner within excel, can you tell me, as i have never come across "sumproduct" and "isnumber" before, can you in layman's terms explain why i would use these functions, so i can understand why i will need to use them, this will help me with my learning to understand excel as i make notes as to why i have used a formula or functionand and this helps me for future reference so i dont need to disturb you,

thank you kitty

4. ## Re: cell totals with blank spaces (Excel 2003)

Hi Kitty

The issue surrounds the matter that you wish to count only cells that are not blank. Therefore if you had content such as a number or a text string your could use COUNTA and can be in this syntax =COUNTA(A1:C13).

Due to your requirement to have minus(-) sign in it which is a text character I was therefore unable to use that function and had to ensure that the cells in the range were in fact a number and there for used ISNUMBER which is boolean and provides a TRUE or FALSE response i.e.

=ISNUMBER(10) is TRUE
=ISNUMBER("-") is FALSE

I am therefore only wanting to count if the cell value in the range is a number and will not work with COUNTIF however if I use

SUMPRODUCT

"Multiplies corresponding components in the given arrays, and returns the sum of those products"

It can count the array items that show true in the given range. My adding 0 to the end converts the value shown to a numeric value to allow further calculations if so required. I hope that helps

5. ## Re: cell totals with blank spaces (Excel 2003)

Why not just use:
=COUNT(G10:G26)

COUNT only counts numbers. Even if you enter a "-" in a cell it will still not be counted...

Steve

6. ## Re: cell totals with blank spaces (Excel 2003)

As to your second question, I am unclear of how you want to enter the "-". Do you just want to enter it at the start when no questions are answered, then you can overwrite it with the number once it is answered.

If you are thinking of something else, how and when do you want to trigger this "validation"? When do you want the "-" to be added to the table?

Steve

7. ## Re: cell totals with blank spaces (Excel 2003)

hi steve this is exactly what i think i want. i was thinking, but at the same time i have to bear in mind that the table may be up dated or altared, i thought "-" was the best fill in for the blank spaces, but is it may be better adding nothing at all, i can add i vaidation so that no more than 4max questions can be answered but im not sure how to do the same validation to prevent a min of four to be answered? would you help me on that to been tryin all nite, im great at word, powerpoint and publisher but pants at this, lol hope you can help, and thanks for the help already
regards kitty

8. ## Re: cell totals with blank spaces (Excel 2003)

My apologies Kitty, I was totally over complicating the answer, I was assuming something completely different, of course COUNT is the answer <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

It does not matter if you change the results, add new results or even delete results the total will change as you make the changes.

Hoping to redeem myself here <img src=/S/grin.gif border=0 alt=grin width=15 height=15> . You could use Conditional Formatting here.

I apologise, it is late here and I am struggling with your English , when you say "to prevent a min of four to be answered", I am assuming you want them to answer 4 or more, it my assumption is correct then:

1) Highlight Cell F10
2) Click Format | Conditioning Format...
3) Change the Window so that Condition 1 is Formula is and type in the following:
=COUNT(\$G\$10:\$J\$10)<3
4) Change colour to Red

4) Repeat but with this formula =COUNT(\$G\$10:\$J\$10)<3

You can use the format Paiinter now to copy the formatting to the other student cells

9. ## Re: cell totals with blank spaces (Excel 2003)

Hi Jerry, sorry about my rubbish english, that was down to a few glasses of wine lol.

I have made the changes as you can see in the attached spreadsheet, and used the format painter, however when igo to ajust the data in cells I14 to O36 it doesnt change red. and even though the rows are set so 4 max and 4 min can be entered as well as adding an error code, i can still get round answering more than 4 entries and if i enter less than 4 as i said its not going red. how can i force these changes, especially so that you cant enter more than 4 entries.

Thanks Kitty

10. ## Re: cell totals with blank spaces (Excel 2003)

Hi Kitty

We just have to change the values to relate and not absolute, like so

=COUNT(\$I11:\$O11)<4

See attached

11. ## Re: cell totals with blank spaces (Excel 2003)

yes thats great jerry, all is working now, except sorting out the rows that are set to 4 max and 4 min, i can still get round answering more than 4 entries how can i force these changes, especially so that you cant enter more than 4 entries.

other wise extra results can be entered in cells, regards kitty

12. ## Re: cell totals with blank spaces (Excel 2003)

> except sorting out the rows that are set to 4 max and 4 min,

Hi Kitty,

I still don't understand what you mean by this 4 max and 4 min as your rows data is about people. Can you notate a workbook with comments to explain your requirement exactly... I am still confused <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

13. ## Re: cell totals with blank spaces (Excel 2003)

Select<code> I11:O36</code>.
Select Data | Validation...
Change the formula to
<code>
=AND(COUNTA(\$I11:\$O11)<=4,ISNUMBER(I11))
</code>
Click OK.
The original version referred to cell <code>\$P11</code> which contains the formula
<code>
=COUNTA(I11:O11)
</code>
but this cell may not yet have been updated when validation occurs, i.e. it may still return 4 when you enter a 5th result. By including the COUNTA formula in the Validation formula itself, you make sure that the correct value is used.

14. ## Re: cell totals with blank spaces (Excel 2003)

right i will try and explain better, if you open up the spreadsheet, bare in mind that this spreadsheet is for the tutors use only, not the student, the tutor updates the spreadsheet as and when the student has grade results.
You have students cells H12:H37 each student must answer 4 out of 7 questions this is a requirement to pass the course and so will fail if they answer less than 4 questions. that is what i ment by (4 max and 4 min). in cells I11:O37 are the marks given to the student for their answer under the question heading they choose, this is entered by the tutor, but if accidently the tutor typed in another result this would mess up the true result. so i need to prevent this, and that is why there must be a validation only allowing 4 results of 4 answered questions.

please say this makes sense as i tried Hans answer and it still doesnt work, also i have unhided coloumn P sorry about that i forgot to unhide it when i sent the first attachment, hope this now makes sence?

regards kitty

15. ## Re: cell totals with blank spaces (Excel 2003)

The validation should prevent more than 4, but even if it is, they will be red (as they will with <4). It will only be green with just 4 entries.

Steve

Page 1 of 2 12 Last

#### Posting Permissions

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