# Need help creating a Counter or descending IF list

#### dj_chris_p

##### New Member
I have dynamic streaming data coming into an Excel sheet.
Let's say the cell where this data is coming into is A1
What I want to do, is count how many times the value of A1 is greater than 5.
I don't mind if this is done either as a counter which increases by 1 every time the condition is met; or if a cell prints a "1" every time the condition is met, and then continues in the cells beneath, so just ending up with a long column of "1"'s which I can then Sum.

I have no idea how to do a counter.

For the second idea, I have created this formula which returns a value of "1" in A2 if the condition is met: =IF(A2="1","1",IF(\$A\$1>=5,"1",""))
This checks if the condition A1>=5 is correct, then returns a value of 1 if it is. The circular reference means that the "1" then remains in A2.
I just don't know how to repeat this in the cells A3 and below to continue the process. I've tried something like this: =IF(A3="1","1",IF(AND(\$A\$1>=5,"1",""),A2="1")) , but it obviously isn't quite correct. The logic should go something like this: IF A1>=5, AND A2="1", then return "1".

This all has to be done using Excel formula rather than VBA, as the spreadsheet is part of another system which uses Excel functions in its spreadsheets.

Also, a thought... If A1>=5, I don't want all the cells below to suddenly return values of "1", which could happen in my mind if they all check at the same time, so is there a way to put a time delay on the next cell down to prevent this?

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
cant you just use a countif?

Code:
``=COUNTIF(A2:A17,">5")``

cant you just use a countif?

Code:
``=COUNTIF(A2:A17,">5")``

No, because I need cells A2 onwards to fill with a value every time A1 meets the criteria. So I don't already have data in those cells that I'm trying to count.

No, because I need cells A2 onwards to fill with a value every time A1 meets the criteria. So I don't already have data in those cells that I'm trying to count.

but the countif will re-calculate when the cells are populated.

but the countif will re-calculate when the cells are populated.

True, but I'm stuck at the stage before that So I need to populate those cells with the values. I can manage to get A2 to work, but it's A3 onwards that I'm stuck on.

I just don't know how to repeat this in the cells A3 and below to continue the process. I've tried something like this: =IF(A3="1","1",IF(AND(\$A\$1>=5,"1",""),A2="1")) , but it obviously isn't quite correct. The logic should go something like this: IF A1>=5, AND A2="1", then return "1".

True, but I'm stuck at the stage before that So I need to populate those cells with the values. I can manage to get A2 to work, but it's A3 onwards that I'm stuck on.

[/COLOR]

ok that is part of your "second part". Lets work on that.

So you have a value in A1.

you want A2 to return a 1 if A1 is greater than 5

this goes in A2
Code:
``=IF(\$A\$1>=5,1,"")``

In A3 you want it also to show a 1 if A1 is >= 5. If that is all just copy the formula down. IF not then I need to understand what else A3 is checking against. Do you only want it to check A1 >= 5 AND A2=1?

Code:
``=if(AND(\$A\$1>=5,A2=1),1,"")``

Replies
1
Views
219
Replies
0
Views
275
Replies
1
Views
371
Replies
6
Views
306
Replies
3
Views
850

1,196,323
Messages
6,014,639
Members
441,833
Latest member
Rangerreeve

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back