formula for text and if correct bring forward a number

[denise]

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

 

[Gord Dibben]

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

 

[denise]

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.

 

[MartinW]

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]

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

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

 

[MartinW]

Your welcome, Denise

Regards
Martin

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