Total remaining formula

I am looking for a points remaining formula that will calculate a column say
from 1-10 and tell me what numbers have been used and what numbers are left.
This will be a running total that calculates point totals so the maximum
total if all requirements are met would be the total of A1:A10 in B10 (55).
But some of the requirements are not always met so the cell will not be
awarded a point value. The formula would be for column C10 where it would
calculate the total possible (55) – points assigned in each cell. Because
this is a running total I have some cells that have not been determined yet.
It will basically go through column A and say ok there are (55) possible
points then it runs through column B and say as of B6 the total is (26) out
of (55) with (24) possible remaining and (5) already forfeited for a total of
(55).


A B C
2 2
1 0
10 10
5 5
4 0
9 9
7 ?(TBD)
8 ?(TBD)
6 ?(TBD)
3 ?(TBD)
A11=55 B11=26 Total of B1:B10 C10= 55-26 (24 remaining and 5
already forfeited)




Thank you in advance,


Judd

Hi!

Try this:

=3DSUMIF(B1:B10,"",A1:A10)&" remaining and "&SUMIF
(B1:B10,0,A1:A10)&" already forfeited"

Biff

>-----Original Message-----
>I am looking for a points remaining formula that will=20
calculate a column say=20
>from 1-10 and tell me what numbers have been used and=20
what numbers are left.=20
>This will be a running total that calculates point totals=20
so the maximum=20
>total if all requirements are met would be the total of=20
A1:A10 in B10 (55). =20
>But some of the requirements are not always met so the=20
cell will not be=20
>awarded a point value. The formula would be for column=20
C10 where it would=20
>calculate the total possible (55) =E2?" points assigned in=20
each cell. Because=20
>this is a running total I have some cells that have not=20
been determined yet. =20
>It will basically go through column A and say ok there=20
are (55) possible=20
>points then it runs through column B and say as of B6 the=20
total is (26) out=20
>of (55) with (24) possible remaining and (5) already=20
forfeited for a total of=20
>(55).
>
>
>A B C
>2 2 =09
>1 0
>10 10
>5 5
>4 0
>9 9
>7 ?(TBD)
>8 ?(TBD)
>6 ?(TBD)
>3 ?(TBD)
>A11=3D55 B11=3D26 Total of B1:B10 C10=3D 55-26=20
(24 remaining and 5=20
>already forfeited)
> =20
>=20
>
>
>Thank you in advance,
>
>
>Judd
>
>.
>

Biff,
Thank you very much. Both of these formulas work great.

Are they both the same formula except for the "" and the "0"? Or are they
meant to be imbeded within each other?

"Biff" wrote:

> Hi!
>
> Try this:
>
> =SUMIF(B1:B10,"",A1:A10)&" remaining and "&SUMIF
> (B1:B10,0,A1:A10)&" already forfeited"
>
> Biff
>
> >-----Original Message-----
> >I am looking for a points remaining formula that will
> calculate a column say
> >from 1-10 and tell me what numbers have been used and
> what numbers are left.
> >This will be a running total that calculates point totals
> so the maximum
> >total if all requirements are met would be the total of
> A1:A10 in B10 (55).
> >But some of the requirements are not always met so the
> cell will not be
> >awarded a point value. The formula would be for column
> C10 where it would
> >calculate the total possible (55) â?" points assigned in
> each cell. Because
> >this is a running total I have some cells that have not
> been determined yet.
> >It will basically go through column A and say ok there
> are (55) possible
> >points then it runs through column B and say as of B6 the
> total is (26) out
> >of (55) with (24) possible remaining and (5) already
> forfeited for a total of
> >(55).
> >
> >
> >A B C
> >2 2
> >1 0
> >10 10
> >5 5
> >4 0
> >9 9
> >7 ?(TBD)
> >8 ?(TBD)
> >6 ?(TBD)
> >3 ?(TBD)
> >A11=55 B11=26 Total of B1:B10 C10= 55-26
> (24 remaining and 5
> >already forfeited)
> >
> >
> >
> >
> >Thank you in advance,
> >
> >
> >Judd
> >
> >.
> >
>