Hi, this is my scenario...

I have a table with 2 (more but they are irrelevant) columns:
Column # and GST

In the # Column I have numbers 1, 2, 3 etc and I manually increase it by one
every time I insert another row. In column GST on the same row as # I have a
$$ amount that I put either $$$, TBA or 'Not Costs'.

e.g. 1 = $5.00, 2 = TBA, 3 = -$6.00 4 = No Costs 5 = $2.00, 6 = No Costs etc.

Now, my questions are...
1. I have a field where I would like to know the last # used.
2. I have another field where I need to have total amount $$ (inc neg amounts)
3. I have another field where I need to know how many are TBA
4. I have one final field where I need to know how many are No Costs.

Thanks in advance for everyones help.
Redsphynx

1. I have a field where I would like to know the last # used.

If your #'s are sequential then the last entry will be the MAX value of the
range.

=MAX(A1:A100)

2. I need to have total amount $$ (inc neg amounts)

=SUM(B:B)

3. I need to know how many are TBA

=COUNTIF(B:B,"TBA")

4. I need to know how many are No Costs.

=COUNTIF(B:B,"No Costs")


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Hi, this is my scenario...

I have a table with 2 (more but they are irrelevant) columns:
Column # and GST

In the # Column I have numbers 1, 2, 3 etc and I manually increase it by
one
every time I insert another row. In column GST on the same row as # I have
a
$$ amount that I put either $$$, TBA or 'Not Costs'.

e.g. 1 = $5.00, 2 = TBA, 3 = -$6.00 4 = No Costs 5 = $2.00, 6 = No Costs
etc.

Now, my questions are...
1. I have a field where I would like to know the last # used.
2. I have another field where I need to have total amount $$ (inc neg
amounts)
3. I have another field where I need to know how many are TBA
4. I have one final field where I need to know how many are No Costs.

Thanks in advance for everyones help.
Redsphynx

Thanks,

But I am having trouble getting the first one to work. I have the others
working great, now I just need this one.

When I enter the formula into the cell it looks like this
"=MAX(A6:A200)" and that is how it is displayed... not the number I need..

Thanks again.

"T. Valko" wrote:

1. I have a field where I would like to know the last # used.

If your #'s are sequential then the last entry will be the MAX value of the
range.

=MAX(A1:A100)

2. I need to have total amount $$ (inc neg amounts)

=SUM(B:B)

3. I need to know how many are TBA

=COUNTIF(B:B,"TBA")

4. I need to know how many are No Costs.

=COUNTIF(B:B,"No Costs")


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Hi, this is my scenario...

I have a table with 2 (more but they are irrelevant) columns:
Column # and GST

In the # Column I have numbers 1, 2, 3 etc and I manually increase it by
one
every time I insert another row. In column GST on the same row as # I have
a
$$ amount that I put either $$$, TBA or 'Not Costs'.

e.g. 1 = $5.00, 2 = TBA, 3 = -$6.00 4 = No Costs 5 = $2.00, 6 = No Costs
etc.

Now, my questions are...
1. I have a field where I would like to know the last # used.
2. I have another field where I need to have total amount $$ (inc neg
amounts)
3. I have another field where I need to know how many are TBA
4. I have one final field where I need to know how many are No Costs.

Thanks in advance for everyones help.
Redsphynx

Ok, that cell is probably formatted as text.

Select the cell in question
Got FormatCells
Select GENERAL
OK out

With the cell still selected press function key F2 then hit ENTER.


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Thanks,

But I am having trouble getting the first one to work. I have the others
working great, now I just need this one.

When I enter the formula into the cell it looks like this
"=MAX(A6:A200)" and that is how it is displayed... not the number I need..

Thanks again.

"T. Valko" wrote:

1. I have a field where I would like to know the last # used.

If your #'s are sequential then the last entry will be the MAX value of
the
range.

=MAX(A1:A100)

2. I need to have total amount $$ (inc neg amounts)

=SUM(B:B)

3. I need to know how many are TBA

=COUNTIF(B:B,"TBA")

4. I need to know how many are No Costs.

=COUNTIF(B:B,"No Costs")


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Hi, this is my scenario...

I have a table with 2 (more but they are irrelevant) columns:
Column # and GST

In the # Column I have numbers 1, 2, 3 etc and I manually increase it
by
one
every time I insert another row. In column GST on the same row as # I
have
a
$$ amount that I put either $$$, TBA or 'Not Costs'.

e.g. 1 = $5.00, 2 = TBA, 3 = -$6.00 4 = No Costs 5 = $2.00, 6 = No
Costs
etc.

Now, my questions are...
1. I have a field where I would like to know the last # used.
2. I have another field where I need to have total amount $$ (inc neg
amounts)
3. I have another field where I need to know how many are TBA
4. I have one final field where I need to know how many are No Costs.

Thanks in advance for everyones help.
Redsphynx

Ok, I have just worked that out, but my problem now is that the boss wants a
3 digit number in that column, so when we type 1 or 12 it will be 001 or 012.
This means he has placed an apostrophe at the start of all numbers up to
100. My question now is, for this formula to work, and for me to have the
preceding zeros, what do I need to do??

Thanks,


"T. Valko" wrote:

Ok, that cell is probably formatted as text.

Select the cell in question
Got FormatCells
Select GENERAL
OK out

With the cell still selected press function key F2 then hit ENTER.


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Thanks,

But I am having trouble getting the first one to work. I have the others
working great, now I just need this one.

When I enter the formula into the cell it looks like this
"=MAX(A6:A200)" and that is how it is displayed... not the number I need..

Thanks again.

"T. Valko" wrote:

1. I have a field where I would like to know the last # used.

If your #'s are sequential then the last entry will be the MAX value of
the
range.

=MAX(A1:A100)

2. I need to have total amount $$ (inc neg amounts)

=SUM(B:B)

3. I need to know how many are TBA

=COUNTIF(B:B,"TBA")

4. I need to know how many are No Costs.

=COUNTIF(B:B,"No Costs")


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Hi, this is my scenario...

I have a table with 2 (more but they are irrelevant) columns:
Column # and GST

In the # Column I have numbers 1, 2, 3 etc and I manually increase it
by
one
every time I insert another row. In column GST on the same row as # I
have
a
$$ amount that I put either $$$, TBA or 'Not Costs'.

e.g. 1 = $5.00, 2 = TBA, 3 = -$6.00 4 = No Costs 5 = $2.00, 6 = No
Costs
etc.

Now, my questions are...
1. I have a field where I would like to know the last # used.
2. I have another field where I need to have total amount $$ (inc neg
amounts)
3. I have another field where I need to know how many are TBA
4. I have one final field where I need to know how many are No Costs.

Thanks in advance for everyones help.
Redsphynx

Typo:

Got FormatCells

Should be:

Goto FormatCells


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok, that cell is probably formatted as text.

Select the cell in question
Got FormatCells
Select GENERAL
OK out

With the cell still selected press function key F2 then hit ENTER.


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Thanks,

But I am having trouble getting the first one to work. I have the others
working great, now I just need this one.

When I enter the formula into the cell it looks like this
"=MAX(A6:A200)" and that is how it is displayed... not the number I
need..

Thanks again.

"T. Valko" wrote:

1. I have a field where I would like to know the last # used.

If your #'s are sequential then the last entry will be the MAX value of
the
range.

=MAX(A1:A100)

2. I need to have total amount $$ (inc neg amounts)

=SUM(B:B)

3. I need to know how many are TBA

=COUNTIF(B:B,"TBA")

4. I need to know how many are No Costs.

=COUNTIF(B:B,"No Costs")


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Hi, this is my scenario...

I have a table with 2 (more but they are irrelevant) columns:
Column # and GST

In the # Column I have numbers 1, 2, 3 etc and I manually increase it
by
one
every time I insert another row. In column GST on the same row as # I
have
a
$$ amount that I put either $$$, TBA or 'Not Costs'.

e.g. 1 = $5.00, 2 = TBA, 3 = -$6.00 4 = No Costs 5 = $2.00, 6 = No
Costs
etc.

Now, my questions are...
1. I have a field where I would like to know the last # used.
2. I have another field where I need to have total amount $$ (inc neg
amounts)
3. I have another field where I need to know how many are TBA
4. I have one final field where I need to know how many are No Costs.

Thanks in advance for everyones help.
Redsphynx

for this formula to work, and for me to have the
preceding zeros, what do I need to do??

Well, if it were me I wouldn't use the apostrophe because now you have 2
different data types. You have text up to 099 and then you have numbers from
100.

I would use a custom NUMBER format of 000. If you use this format and type
in the number 1 it will display as 001 and it will still be a numeric number
then the MAX formula would work.

If you want to do as I suggested you can convert the text numbers to numeric
numbers very easily:

Select the range of cells that contain the text numbers
Goto DataText to Columns
Just click Finish
That should convert the text numbers to numeric numbers

Now, apply the custom number format
With the range still selected
Goto FormatCells
Select CUSTOM
In the little box under Type:, enter 000
OK out

--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Ok, I have just worked that out, but my problem now is that the boss wants
a
3 digit number in that column, so when we type 1 or 12 it will be 001 or
012.
This means he has placed an apostrophe at the start of all numbers up to
100. My question now is, for this formula to work, and for me to have the
preceding zeros, what do I need to do??

Thanks,


"T. Valko" wrote:

Ok, that cell is probably formatted as text.

Select the cell in question
Got FormatCells
Select GENERAL
OK out

With the cell still selected press function key F2 then hit ENTER.


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Thanks,

But I am having trouble getting the first one to work. I have the
others
working great, now I just need this one.

When I enter the formula into the cell it looks like this
"=MAX(A6:A200)" and that is how it is displayed... not the number I
need..

Thanks again.

"T. Valko" wrote:

1. I have a field where I would like to know the last # used.

If your #'s are sequential then the last entry will be the MAX value
of
the
range.

=MAX(A1:A100)

2. I need to have total amount $$ (inc neg amounts)

=SUM(B:B)

3. I need to know how many are TBA

=COUNTIF(B:B,"TBA")

4. I need to know how many are No Costs.

=COUNTIF(B:B,"No Costs")


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Hi, this is my scenario...

I have a table with 2 (more but they are irrelevant) columns:
Column # and GST

In the # Column I have numbers 1, 2, 3 etc and I manually increase
it
by
one
every time I insert another row. In column GST on the same row as #
I
have
a
$$ amount that I put either $$$, TBA or 'Not Costs'.

e.g. 1 = $5.00, 2 = TBA, 3 = -$6.00 4 = No Costs 5 = $2.00, 6 = No
Costs
etc.

Now, my questions are...
1. I have a field where I would like to know the last # used.
2. I have another field where I need to have total amount $$ (inc
neg
amounts)
3. I have another field where I need to know how many are TBA
4. I have one final field where I need to know how many are No
Costs.

Thanks in advance for everyones help.
Redsphynx

P.S.

Then apply the same custom number format to the cell that holds the MAX
formula.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
for this formula to work, and for me to have the
preceding zeros, what do I need to do??

Well, if it were me I wouldn't use the apostrophe because now you have 2
different data types. You have text up to 099 and then you have numbers
from 100.

I would use a custom NUMBER format of 000. If you use this format and type
in the number 1 it will display as 001 and it will still be a numeric
number then the MAX formula would work.

If you want to do as I suggested you can convert the text numbers to
numeric numbers very easily:

Select the range of cells that contain the text numbers
Goto DataText to Columns
Just click Finish
That should convert the text numbers to numeric numbers

Now, apply the custom number format
With the range still selected
Goto FormatCells
Select CUSTOM
In the little box under Type:, enter 000
OK out

--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Ok, I have just worked that out, but my problem now is that the boss
wants a
3 digit number in that column, so when we type 1 or 12 it will be 001 or
012.
This means he has placed an apostrophe at the start of all numbers up to
100. My question now is, for this formula to work, and for me to have
the
preceding zeros, what do I need to do??

Thanks,


"T. Valko" wrote:

Ok, that cell is probably formatted as text.

Select the cell in question
Got FormatCells
Select GENERAL
OK out

With the cell still selected press function key F2 then hit ENTER.


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Thanks,

But I am having trouble getting the first one to work. I have the
others
working great, now I just need this one.

When I enter the formula into the cell it looks like this
"=MAX(A6:A200)" and that is how it is displayed... not the number I
need..

Thanks again.

"T. Valko" wrote:

1. I have a field where I would like to know the last # used.

If your #'s are sequential then the last entry will be the MAX value
of
the
range.

=MAX(A1:A100)

2. I need to have total amount $$ (inc neg amounts)

=SUM(B:B)

3. I need to know how many are TBA

=COUNTIF(B:B,"TBA")

4. I need to know how many are No Costs.

=COUNTIF(B:B,"No Costs")


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Hi, this is my scenario...

I have a table with 2 (more but they are irrelevant) columns:
Column # and GST

In the # Column I have numbers 1, 2, 3 etc and I manually increase
it
by
one
every time I insert another row. In column GST on the same row as #
I
have
a
$$ amount that I put either $$$, TBA or 'Not Costs'.

e.g. 1 = $5.00, 2 = TBA, 3 = -$6.00 4 = No Costs 5 = $2.00, 6 = No
Costs
etc.

Now, my questions are...
1. I have a field where I would like to know the last # used.
2. I have another field where I need to have total amount $$ (inc
neg
amounts)
3. I have another field where I need to know how many are TBA
4. I have one final field where I need to know how many are No
Costs.

Thanks in advance for everyones help.
Redsphynx

Thanks, that works when the =max formula is used in the same column, but when
the # is column A and the formula is in column G it doesn't work. Any ideas?

"T. Valko" wrote:

Typo:

Got FormatCells

Should be:

Goto FormatCells


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok, that cell is probably formatted as text.

Select the cell in question
Got FormatCells
Select GENERAL
OK out

With the cell still selected press function key F2 then hit ENTER.


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Thanks,

But I am having trouble getting the first one to work. I have the others
working great, now I just need this one.

When I enter the formula into the cell it looks like this
"=MAX(A6:A200)" and that is how it is displayed... not the number I
need..

Thanks again.

"T. Valko" wrote:

1. I have a field where I would like to know the last # used.

If your #'s are sequential then the last entry will be the MAX value of
the
range.

=MAX(A1:A100)

2. I need to have total amount $$ (inc neg amounts)

=SUM(B:B)

3. I need to know how many are TBA

=COUNTIF(B:B,"TBA")

4. I need to know how many are No Costs.

=COUNTIF(B:B,"No Costs")


--
Biff
Microsoft Excel MVP


"Redsphynx" wrote in message
...
Hi, this is my scenario...

I have a table with 2 (more but they are irrelevant) columns:
Column # and GST

In the # Column I have numbers 1, 2, 3 etc and I manually increase it
by
one
every time I insert another row. In column GST on the same row as # I
have
a
$$ amount that I put either $$$, TBA or 'Not Costs'.

e.g. 1 = $5.00, 2 = TBA, 3 = -$6.00 4 = No Costs 5 = $2.00, 6 = No
Costs
etc.

Now, my questions are...
1. I have a field where I would like to know the last # used.
2. I have another field where I need to have total amount $$ (inc neg
amounts)
3. I have another field where I need to know how many are TBA
4. I have one final field where I need to know how many are No Costs.

Thanks in advance for everyones help.
Redsphynx