Thread: Exclude cells in calculation (Excel 2003)

1. Exclude cells in calculation (Excel 2003)

I have a spreadsheet that I am using that I would like to do a calculation that excludes cells in a column, if the last entry in a column is blank.
I have students turning in assignments early, and I don't want their grade percentage inflated. I do not enter the points for the assignment (in the bottom row) until the assignment is due. I'd like the calculation to exclude the numbers, in the column, above the TOTAL POINTS cell.

I have attached a copy of the spreadsheet.
I am entering the students points for the assignments in columns H through AG. Column AH totals the points. I would like the total in AH to 'look' at the total points row (row 8) and if a cell is blank to exclude the the numbers above it in the total in AH.

The example in the attachment: Gradebook.xls
Column W has an entry in row 5 (he handed in the assignment early), but I have not entered in the points possible in column W - row 8. Is there a way to exclude the entry in column W - row 5 from the total in column AH - row 5?

Hope this makes sense!

Thanks,

Bart Putnam

2. Re: Exclude cells in calculation (Excel 2003)

Check to see if this will work: =SUM(IF(H8:AG8="",0,H5:AG5))

Note: enter it as an array formula by pressing CTRL + SHIFT+ENTER

PS: Welcome to the lounge...

3. Re: Exclude cells in calculation (Excel 2003)

Welcome to Woody's Lounge!

You can use the SUMIF function for this:
<code>
=SUMIF(\$H\$8:\$AG\$8,"<>",H5:AG5)
</code>
The first argument in SUMIF is the range on which you want to test. I have made the reference absolute, so that it will not change if you fill the formula down to AH6 and AH7.
The second argument is the condition, in the form of a quoted string. <> means not blank.
The third argument is the range to sum. This reference is relative, so that it will be adjusted automatically if you fill down.

4. Re: Exclude cells in calculation (Excel 2003)

Thanks !!!!

I will try them both when I get a chance (actually supposed to be working).

Bart

5. Re: Exclude cells in calculation (Excel 2003)

Hans,
Worked beautifully - Thanks!

Rudi,
Not sure how to enter the formula "by pressing CTRL + SHIFT+ENTER"
Not familiar with array formulas (but would like to learn).
Thanks!

6. Re: Exclude cells in calculation (Excel 2003)

Simply type Rudi's formula in the formula bar (or copy and paste it), and then, instead of pressing Enter or clicking the green checkmark button, press Ctrl+Shift+Enter, i.e. hold down Ctrl and Shift, then press Enter, then release Ctrl and Shift.

7. Re: Exclude cells in calculation (Excel 2003)

Thanks Hans,
I got the array formula to work.

Thanks again!!

Posting Permissions

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