summing values in one row based on values in another row

[Bert]

I have several rows of scores, each score in a separate column. (One row
per individual.)
The first row of scores contains the perfect scores (highest scores
possible) for each column.
The second column in each row contains the total of all scores in that row.
(The second column in the first row thus contains the perfect score total.)
In the first column of each row (except the first one), I have the following
formula: =ROUND(((100*B2)/($B$1)),0), where B2 contains the total of all
scores in Row 2, and $B$1 contains the perfect total score. This yields--in
this example--a percentage score of row 2's total compared to the perfect
score total.
So far, everything's great.however.sometimes I don't want to count a
particular score for a given individual/row. In this situation, I enter "E"
(for "excused") in the appropriate cell.
QUESTION: I would like to modify the formula (perhaps replacing
$B$1.or.subtracting a calculated value from it), so that for each row, if
there's one or more "E's" in the row, the formula will adjust the perfect
score total by NOT including the corresponding perfect score (the perfect
score in the same column as the "E") in the perfect score total in the
formula.
I'd appreciate any suggestions! (Alternatively, I could, I suppose, do this
a Macro and run it whenever an E is added to a cell, but a formula seems the
better solution.)
Bert
(I sent a similar post a week or so ago and included a spreadsheet sample,
but the formatting got messed up, so rather that clarify, it only muddied
the waters more, and I don't know of any way to link the actual spreadsheet.
Sorry.)

 

[Sandy Mann]

Bert,

If I follow you correctly then try:

=ROUND(((100*B2)/($B$1-SUMPRODUCT((C2:K2="E")*C1:K1))),0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bob Phillips]

why not the more efficient

=ROUND((100*B2)/($B$1-SUMIF(C2:K2,"E",C1:K1)),0)



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bert]

I've tried both Sandy's formula and Bob's. Both seem to work equally well.
Thanks to both of you!
Is there a functional equivalence to the two formula's? I'm enough of a
formula novice that I could possible say.
(It's enough to see that they're working!)
Thanks again.
Bert

 

[Bob Phillips]

Bert,

They are functionally equivalent, I just presented the alternative as SUMIF
is more efficient than SUMPRODUCT. SP is generally used with more than one
condition, and there is only one here, so it is not necessary.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sandy Mann]

why not the more efficient

Why not indeed sir! <g>

I have noticed in the NG's - and I include myself in this - that because
SUMPRODUCT() can be used as a cover-all sticking plaster to use it rather
than think the solution through.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bob Phillips]

Why not indeed sir! <g>

I have noticed in the NG's - and I include myself in this - that because
SUMPRODUCT() can be used as a cover-all sticking plaster to use it rather
than think the solution through.

Guilty also!