# Loop in Excel 2007

#### lottrup

##### New Member
Hi,

I have a problem which I hope someone can help me with.
In my spreadsheet I want to count the number of cells that meets a condition. I have tried to use the COUNTIF function, but in thic case I want the condition to specify the area (ex E2:E19) and then the function should count all the cells of this kind.
To be more specific the column on which I set the condition consists of seconds (an increasing set of numbers) and I want to count the cells that go 60 seconds back.

I really hope someone can help me.

/Nana

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Yard

##### Well-known Member
Hi and welcome,

Sorry, but your explanation is not clear (to me). What do you want to count and what are the conditions upon which you want the count to be based?

Can you post a simple example of your data, making a clear distinction between what you have and what you want.

#### lottrup

##### New Member
Okay, I'll try again then

Shortly I want to count all the rows (observations) that that have been captured within the last 60 seconds. The seconds are in a column (Unix timestamp) and of course the number of rows that I want to count will vary as the interval of seconds between observations vary. So I want to count all the rows from a certain point (60 seconds back in time) and forward to the current cell/row.
I have 65000 observations at the time, so this could be very very helpful.

Thanks.

#### Yard

##### Well-known Member
With your timestamps in column B, and this in cell C1 (or anywhere else you choose):

=NOW()-"00:01:00"

then use this to count the values that fall within 60 seconds in the past:

=SUMPRODUCT(--(B1:B10>=\$C\$1))

#### tekkiegurl

##### New Member
i got confused on the question

#### Yard

##### Well-known Member
What question? Do you mean you don't understand my solution, or do you mean you don't understand what you want?

My solution:

Put this in cell C1:
=NOW()-"00:01:00"

Now use this formula to count how many values in the range B1:B10 are greater than equal to the value in C1 (which will always be 1 minute in the past):

=SUMPRODUCT(--(B1:B10>=\$C\$1))

Is that clearer?<!-- / message --><!-- sig -->

#### lottrup

##### New Member
Thank you so much, but perhaps I just don't understand your answer completely.

I guess that the NOW() function creates the current date and time, but I don't see why this is nescesary when I want to count the observations 60 seconds back in time from the timestamp in ex column B?

When you write B1:B10 in the SUMPRODUCT function - should I place this in the 10th row, and how can I make it dynamic so that it will always count all the obervations 60 seconds back in time from the timestamp?

And just because I'm new with this I would like to know what the "--" means in the SUMPRODUCT function? And also what the \$ signs mean when you write \$C\$1?

#### Yard

##### Well-known Member
I guess that the NOW() function creates the current date and time,

Yes it does, and the

-"00:01:00"

part subtracts one minute from the current date and time, thereby returning the time value for one minute in the past.

but I don't see why this is nescesary when I want to count the observations 60 seconds back in time from the timestamp in ex column B?

If you didn't have that, how do you expect Excel to know to what it should compare the timestamp in column B? i.e. Column B contains 12:30:46 and you want Excel to know whether to count this cell...so you must somehow tell it what you are comparing it to.

When you write B1:B10 in the SUMPRODUCT function - should I place this in the 10th row

No, you put the formula wherever you want the counted result to appear. The B1:B10 refers to the range containing all the timestamp values - you just adjust it to suit. I think you said you have ~65000 values, so maybe rather than a range, just specify the whole column using B:B (but don't expect lightning-fast calculation time!)

and how can I make it dynamic so that it will always count all the obervations 60 seconds back in time from the timestamp?

The formula is dynamic in the sense that, every time the worksheet is calculated, it will count how many timestamp values are greater than the value in C1, and the value in C1 will always equal the time it was one minute ago.

And just because I'm new with this I would like to know what the "--" means in the SUMPRODUCT function?

It converts TRUE values to 1 and FALSE values to 0, all of which are then summed to give you a total of the number of times the condition is TRUE. The condition is the

B1:B10>=\$C\$1

part.

So if 3 values within B1:B10 are greater than or equal to the value in C1, the formula counts 3 TRUE values, which is 3 * 1 = 3.

And also what the \$ signs mean when you write \$C\$1?

It locks the reference to C1, so that wherever you copy your formula it will always refer to C1.

Have you actually tried implementing the formulas with unexpected results, or are you pre-judging the validity of it?

#### lottrup

##### New Member
Now I completely understand what you mean and it works perfectly
Thank you so much for your time and help!

Replies
11
Views
251
Replies
0
Views
200
Replies
1
Views
133
Replies
19
Views
839
Replies
12
Views
263

1,191,272
Messages
5,985,684
Members
439,974
Latest member
sjoerdbosch

### 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