**Question description:**

*This user has given permission to use the problem statement for this blog.*

i have a column of numbers(d column), in the e column i want the cells to return nothing if the d column is not a 12 and if the d column is a 12 i want the e column to return the number of cells since the last 12.

Solved by O. J. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
05/10/2018 - 05:28

Hi

Excelchat Expert
05/10/2018 - 05:28

Welcome to Got it Pro

User
05/10/2018 - 05:28

hi

Excelchat Expert
05/10/2018 - 05:28

Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.

Excelchat Expert
05/10/2018 - 05:28

Please tell a bit about the requirement so that i can help better

User
05/10/2018 - 05:29

so on my spreadsheet when i press f9 the number in my table all change, when i press f9 if column d is a 12 i would like column e to count how many cells since the last 12

User
05/10/2018 - 05:31

so in row 32 there is a 12 in column d, so column e displays an 18 because there are 18 cells between 32 and the last 12 in row 14

Excelchat Expert
05/10/2018 - 05:42

We can make the formula but we will need to make slight adjustments after first 12 is found and so on

User
05/10/2018 - 05:43

like what kind of adjustment, i have 10,000 rows on my shreadsheet

Excelchat Expert
05/10/2018 - 05:43

Then let me figure out a better way

Excelchat Expert
05/10/2018 - 06:07

Hey are you there?

User
05/10/2018 - 06:07

yes

Excelchat Expert
05/10/2018 - 06:07

Can you see column I?

User
05/10/2018 - 06:08

yes

Excelchat Expert
05/10/2018 - 06:08

I have conditional formatted the cells where we want to see the count

Excelchat Expert
05/10/2018 - 06:08

Do you see cells in green?

User
05/10/2018 - 06:08

yes

Excelchat Expert
05/10/2018 - 06:08

Then in column I have used this formula for I15

Excelchat Expert
05/10/2018 - 06:08

=IF(I13=I14,1,IF(H15<>0,I14+1,I14))

Excelchat Expert
05/10/2018 - 06:09

If previous 2 values before this are equal then count should start from 1

Excelchat Expert
05/10/2018 - 06:09

Do you see?

User
05/10/2018 - 06:09

yeas

Excelchat Expert
05/10/2018 - 06:09

Using this logic you can drag this down to as many rows and will get the right answer

Excelchat Expert
05/10/2018 - 06:10

Please see and let me know if this helps

User
05/10/2018 - 06:10

ok 1 sec

Excelchat Expert
05/10/2018 - 06:12

Did you see?

Excelchat Expert
05/10/2018 - 06:12

Does it help you out?

Excelchat Expert
05/10/2018 - 06:12

this is the best method i could think of using formulas

User
05/10/2018 - 06:13

im trying to get it to work on my sheet

Excelchat Expert
05/10/2018 - 06:13

otherwise it can be done by VBA but this platform does not support vba

Excelchat Expert
05/10/2018 - 06:13

ok

Excelchat Expert
05/10/2018 - 06:13

Conditional formatting is important here

Excelchat Expert
05/10/2018 - 06:13

ones which are green

Excelchat Expert
05/10/2018 - 06:13

if the column value next to it is 0 then green

User
05/10/2018 - 06:14

ok

User
05/10/2018 - 06:15

so column i needs to have conditional formatting?

Excelchat Expert
05/10/2018 - 06:15

Not necessarily

Excelchat Expert
05/10/2018 - 06:16

I just populated the required value in front in column E as per your initial ask based on values in column I

Excelchat Expert
05/10/2018 - 06:16

Just forllow this and you will be able to do this

Excelchat Expert
05/10/2018 - 06:16

Please do give good rating and reviews, it would really mean a lot

User
05/10/2018 - 06:17

i think this will work but when i put it in my sheet i am getting a 2 in row 14

User
05/10/2018 - 06:17

the column nect to it is a 0 like it should be

User
05/10/2018 - 06:18

=IF(D14<>12,1,0)

User
05/10/2018 - 06:18

reads 0

Excelchat Expert
05/10/2018 - 06:18

Just use the same formulas as i have used

Excelchat Expert
05/10/2018 - 06:18

Please see the formulas correctly

User
05/10/2018 - 06:26

something isn't working my h14 is reading 1

User
05/10/2018 - 06:26

=IF(D14<>12,1,0)

Excelchat Expert
05/10/2018 - 06:26

Can you give your data i can make it for you

Excelchat Expert
05/10/2018 - 06:26

if this is working here it should work in your file as well

Excelchat Expert
05/10/2018 - 06:26

I think you have pasted the formula one row up or down

Excelchat Expert
05/10/2018 - 06:26

that could be the reason

Excelchat Expert
05/10/2018 - 06:27

Please adjust it as i have done

Excelchat Expert
05/10/2018 - 06:27

Time is about to go

Excelchat Expert
05/10/2018 - 06:27

I hope you willl give good ratings

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*