formula for text and if correct bring forward a number

D

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
 
G

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
 
D

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.
 
M

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
 
D

denise

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

MartinW said:
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
Click to expand...
 
Top