formula for text and if correct bring forward a number
 

In an excel spreadsheet I have text in a cell and numbers in other cells in
same row. I want to create a couple of formulas the first being that in cell
A2 says "open" then bring forward the number "1"

the other being that if cell A2 says "open" then bring forward the entire row

I'm not sure how to create this formula whether it would be something like a
=what if or =if

formula for text and if correct bring forward a number
 

Explain what "bring forward" means.

Also I think you have a typo because you used A2 twice with "open" returning
different results.

For the first formula maybe in A3 enter =IF(A2="open",1,"")


Gord Dibben MS Excel MVP

On Fri, 11 Jan 2008 11:49:04 -0800, denise
wrote:


In an excel spreadsheet I have text in a cell and numbers in other cells in
same row. I want to create a couple of formulas the first being that in cell
A2 says "open" then bring forward the number "1"

the other being that if cell A2 says "open" then bring forward the entire row

I'm not sure how to create this formula whether it would be something like a
=what if or =if

formula for text and if correct bring forward a number
 

Thank you that answered the first part of my question, which brings up
another question. In the spreadsheet , with several rows of data, you have a
column that lists status i.e. open, closed or pending. Can you change that
formula to give you the total number of rows that have "open", "closed" or
"pending" status?

by bring forward I just mean produce an answer to the formula.



"Gord Dibben" wrote:

Explain what "bring forward" means.

Also I think you have a typo because you used A2 twice with "open" returning
different results.

For the first formula maybe in A3 enter =IF(A2="open",1,"")


Gord Dibben MS Excel MVP

On Fri, 11 Jan 2008 11:49:04 -0800, denise
wrote:


In an excel spreadsheet I have text in a cell and numbers in other cells in
same row. I want to create a couple of formulas the first being that in cell
A2 says "open" then bring forward the number "1"

the other being that if cell A2 says "open" then bring forward the entire row

I'm not sure how to create this formula whether it would be something like a
=what if or =if

formula for text and if correct bring forward a number
 

Hi Denise,

Try this,
Assuming your open, closed and pending column is C

In J1 put
open
In J2 put
closed
In J3 put
pending

In K1 put
=COUNTIF(C:C,J1)
and drag it down to K3

You will need to adjust ranges and cells to suit your sheet.
Is that the sort of thing you are looking for?

HTH
Martin


"denise" wrote in message
...
Thank you that answered the first part of my question, which brings up
another question. In the spreadsheet , with several rows of data, you have
a
column that lists status i.e. open, closed or pending. Can you change that
formula to give you the total number of rows that have "open", "closed" or
"pending" status?

by bring forward I just mean produce an answer to the formula.



"Gord Dibben" wrote:

Explain what "bring forward" means.

Also I think you have a typo because you used A2 twice with "open"
returning
different results.

For the first formula maybe in A3 enter =IF(A2="open",1,"")


Gord Dibben MS Excel MVP

On Fri, 11 Jan 2008 11:49:04 -0800, denise

wrote:


In an excel spreadsheet I have text in a cell and numbers in other cells
in
same row. I want to create a couple of formulas the first being that in
cell
A2 says "open" then bring forward the number "1"

the other being that if cell A2 says "open" then bring forward the
entire row

I'm not sure how to create this formula whether it would be something
like a
=what if or =if

formula for text and if correct bring forward a number
 

yes, thank you both very much for your help ;-)

"MartinW" wrote:

Hi Denise,

Try this,
Assuming your open, closed and pending column is C

In J1 put
open
In J2 put
closed
In J3 put
pending

In K1 put
=COUNTIF(C:C,J1)
and drag it down to K3

You will need to adjust ranges and cells to suit your sheet.
Is that the sort of thing you are looking for?

HTH
Martin


"denise" wrote in message
...
Thank you that answered the first part of my question, which brings up
another question. In the spreadsheet , with several rows of data, you have
a
column that lists status i.e. open, closed or pending. Can you change that
formula to give you the total number of rows that have "open", "closed" or
"pending" status?

by bring forward I just mean produce an answer to the formula.



"Gord Dibben" wrote:

Explain what "bring forward" means.

Also I think you have a typo because you used A2 twice with "open"
returning
different results.

For the first formula maybe in A3 enter =IF(A2="open",1,"")


Gord Dibben MS Excel MVP

On Fri, 11 Jan 2008 11:49:04 -0800, denise

wrote:


In an excel spreadsheet I have text in a cell and numbers in other cells
in
same row. I want to create a couple of formulas the first being that in
cell
A2 says "open" then bring forward the number "1"

the other being that if cell A2 says "open" then bring forward the
entire row

I'm not sure how to create this formula whether it would be something
like a
=what if or =if

formula for text and if correct bring forward a number
 

Your welcome, Denise

Regards
Martin


"denise" wrote in message
...
yes, thank you both very much for your help ;-)

"MartinW" wrote:

Hi Denise,

Try this,
Assuming your open, closed and pending column is C

In J1 put
open
In J2 put
closed
In J3 put
pending

In K1 put
=COUNTIF(C:C,J1)
and drag it down to K3

You will need to adjust ranges and cells to suit your sheet.
Is that the sort of thing you are looking for?

HTH
Martin


"denise" wrote in message
...
Thank you that answered the first part of my question, which brings up
another question. In the spreadsheet , with several rows of data, you
have
a
column that lists status i.e. open, closed or pending. Can you change
that
formula to give you the total number of rows that have "open", "closed"
or
"pending" status?

by bring forward I just mean produce an answer to the formula.



"Gord Dibben" wrote:

Explain what "bring forward" means.

Also I think you have a typo because you used A2 twice with "open"
returning
different results.

For the first formula maybe in A3 enter =IF(A2="open",1,"")


Gord Dibben MS Excel MVP

On Fri, 11 Jan 2008 11:49:04 -0800, denise

wrote:


In an excel spreadsheet I have text in a cell and numbers in other
cells
in
same row. I want to create a couple of formulas the first being that
in
cell
A2 says "open" then bring forward the number "1"

the other being that if cell A2 says "open" then bring forward the
entire row

I'm not sure how to create this formula whether it would be something
like a
=what if or =if