#### i8ig

##### Board Regular
I have a spread sheet that has information in numbers, text and dates. I need to be able to write a formula in a cell K30 that does the following:

Look at column 21(U) and if "Settled" appears to count the number of times this occurs, then to take the total and divide it by the number that is a result of: Looking into Column 1(A), finding all the rows which have 'Nbar' and then Looking into column 13(M) and if the date in this column that corresponds to the row with 'Nbar' and the date is =to today or before today and place the result in K30. The only other variable is that no cell past the cell in Column 21(U) where the last 'Settled' appears be counted. I would like to have this cell update automatically as 'Settled' is entered.

I have no idea where to start.

Any help is appreciated, and thanks in advance

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Gary Drumm

##### Active Member
Can you post a sample of your sheet, and give an example of what you want the expected result to look like?

At this point, I'm not clear on exactly what you want it to do.

Use Colo's HTML maker.
Gary

#### i8ig

##### Board Regular
I am unable to post sheet due to company not allowing down load of Colo's HTML maker. I could email you a sheet if that will help?

What I need is a number in K30 that would be the sum of the formula I asked about

#### cpis0002

##### Board Regular
Hi, I didnt understand the last sentence... as for the rest:

Create a column and drag this formula throught the column:

=IF(AND(A10="Nbar",M10<=\$B\$1),"Y","N")

Just change the row number in order to select the rows you have. \$B\$1 is a fixed reference cell with the formula =NOW()

then use the formula:

=(COUNTIF(U10:U500,"Settled"))/(COUNTIF(range of new column,"Y"))

Obviously adjust the range for the data you have. If you need anymore assistance or would like me to apply any of these changes, just send me an email on chrispisani@gmail.com

#### i8ig

##### Board Regular
Thanks very much, I now can see how this works!!

#### cpis0002

##### Board Regular
no probs, if u need anything else, just lemme know... i love a challenge... as long as it has nothing todo with visual basic, lol

Replies
0
Views
111
Replies
3
Views
187
Replies
2
Views
235
Replies
3
Views
338
Replies
3
Views
176

1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

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