Tracker
HOME    TRACKER    Excel

# Sum Values In Column If Condition Met In Another

## If possible, I would love for the answer to be a "formula" and not a macro, but if it must be a macro, then that's doable too: Basically, I would love a formula that would add all the numeric values in "Column A", as long as "Column B" in that same row="YES". So... 4 YES 6 YES 7 NO 9 YES would mean 4 + 6 + 9 = 19 This will cover a variable amount of rows, but it will definitely be less than a few hundred.

Related Forum Messages:
Sum Values In Column Based On Condition In Another
II want to sum the values under 'amount' column only if the corresponding value under 'Balance' column is "Paid"

NameAmount Start Date End Date No.of DaysInteresetBalance
Sat1 250 1-Aug-07 20-Aug-07 19 - "paid"
Sat2 550 4-Aug-07 20-Aug-07 16 -
Sat3 250 10-Aug-07 20-Aug-07 10 - "paid"
Sat4 450 6-Aug-07 20-Aug-07 14 -

Total PAIDAMOUNT(B1:B4)

For the above, the sum value should be 500. I have written the following function to calculate the same. On uncommenting the line 'paid = CStr(paidvar)' the value is displayed as '#VALUE!'. On uncommenting all commented lines, the value is displayed as 'Paid'.

Function PAIDAMOUNT(amountRange As Range) As Variant
Dim count As Long
Dim paidvar As Variant
Dim paid As String
Application.Volatile True
PAIDAMOUNT = 0
For count = 1 To amountRange.Cells.count
paidvar = amountRange.Offset(count - 1, 7 - amountRange.Column).Value
'paid = CStr(paidvar).................

Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days

17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7

On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.

Condition Paste Formula In Column N Based On Column A
I am wanting to paste formula from N1 till N X (X = varaiable row) where X reliant on Column A Row X. If Column A Row X has character "=====" it should paste N1 Formula all the way down till N Row X which is equal to A Row X containing "=====" .

I might be sounding complicated over here but it is a simple equation.. I have tried to approach this in the capacity i could by condition if Column A row x is blank delete the row.

Match The Row Values On Condition
I need a macro to match the row values on condotion.

To match the Q column values with S column values.

If suppose i have data i lke this then,

Q| R |S
1|22 yes 22
2|33 no 38
3|35 no 42
4|38 no 45
5|42 no 50
6|45 no
7|50 no

then the output should be like

Q| R |S
1|22 yes 22
2|33 no
3|35 no
4|38 yes 38
5|42 yes 42
6|45 yes 45
7|50 yes 50

IF Condition True THEN AVERAGE The Values
I'm looking for some help. I need a function that can check if each of two seperate values is less than one. If less than one then divide by 2. Then give me the average of these two values. So it could be that it takes 0.5/2=0.25 for one value and 3 for another to give me the average of 1.625.

Highest To Lowest Values By Condition
I need a formula that will pull specified information from sheet2 (without having to sort) into sheet 1 by looking up the specified name. See attachd file.

Count Consecutive Values By Condition
I have a spreadsheet with employees performance results for the last 12 months. Column A contains name, columns B to M contain text "Gold", "silver" or "bronze". I need to count how many consecutive months thay have achieved "Gold" status BUT must include latest month.

So, if column M contains "silver" or "bronze", the consecutive answer (column N) would be 0. If col M = "Gold" but L doesn't then N = 1. If M and L = "Gold" then N = 2 etc etc.

Is there a formula I can use in column N for this without doing 12 "if" functions?
I think this needs to be an array formula but the ones I've tried I can't get to work.

List N Top Values By Single Condition
I want 3 extra columns in the attached sheet which is part of a 62000 row sheet.

column d..I want a formula for the lowest ip number(COLUMN C) in each event( event number in column1) printed on all rows in column d fo that event...

column e...second lowest ip number for each event..

coolumn f..third lowest ip number for each event..

Sum Values From Multiple Worksheets Based On A Condition
I'm trying to sum across multiple worksheets based on a simple condition. I have a time sheet with a worksheet for each week (52 worksheets). I've gone back and added a column to code the type of work done, types 1 - 17. I want to sum the hours of each type across all worksheets. This is what I have that doesn't work:

ChoosE Multiple Values For The Second Condition In The Range
I am having problems with choosing multiple values for the second condition in the b range. The formula works for one account in the array, but not multiples.

=SUMPRODUCT((('Data Sheet'!\$A\$2:\$A\$997<2110000)+('Data Sheet'!\$A\$2:\$A\$997>2119999)),--('Data Sheet'!\$B\$2:\$B\$997={"300100","302100"}),'Data Sheet'!D\$2:D\$997)

Multiple Condition SUMIF: Sum The Values Between Q77:Q176
I want use the formula sumif...something like that! =SUMIF(I77:I176;AND(>F1092;<F1091);Q77:Q176). I want sum the values between q77:Q176 if the value in i77:i176 is >F1092 and <F1091 But the multiple criteriun don´t work in the sumif.

Find & Replace Values Based On Condition
I have a simple problem I am trying to find a set a values on a column and what to replace once found with the first 5 most left characters. Example:

in column A I can have text and numbers; the numbers that I want to find are in the following format xy:zw / xy:zw on each case the values can be from 0 to 9 so this is tons of posible combinations, I been trying to use **:** ? **:** but I don't get none. This is what I am doing

Set target = Sheets("Sheet1"). Range("A1:A9000")
For Each cell In target
If cell.Value = "**:** ? **:**" Then cell.Value = "00.00"
Next cell

A example would be 02:47 / 03:15 the next row could have 03:15 / 04:09
and so on however I could have text in some rows. In reality I would like to find and replace the cell.value with the Left 5 characters if the cell contains ANY value maching that format. Text in that column will never have that format combination

Color Whole Row If Condition Met In A Column
I have a spreadsheet and in the "I" column, it can read either "OPEN" r "CLOSED". If that reads "OPEN", I want the whole row to turn green, if "CLOSED" I want it to turn red. I have fiddled around with conditional formatting and can seem only to change that one cell!

Subtotal With Condition (only Count Values Greater Than Zero When Data Is Filtered)
=SUBTOTAL(2,A2:A100) can be used in combination with any other formula so that when the data is filtered using autofilter it will only count values greater than zero.

I have attached a worksheet with the example.

In the attached workbook subtotal formulas are used in Row1 for sum and row 2 for count.
When the data is filtered using code "DUP" in column D, the subtotal in cell C2 is "4", whereas in the specified range there is a positive value in only 1 cell that is \$1050 in cell C99.

Is there some combination that can be used with SUBTOTAL formula so that it will only count values greater than zero when data is filtered.

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

Formula To Count MIN Of Column With A Condition
I have a formula that tests the minimum time in a column. If the time is the minimum it gets a PB (Personal Best) notation. My problem is once a season there is a competion that needs to be recorded on the worksheet however I do not want this time for this competion counted in the MIN test or to get a PB allocated. I have worked out how to ignore the PB but I cannot get the MIN test to work properly. Dates of comp are in Col B, data (times) in Col C and formula for PB's in Col D. When a cell in Col B (Dates of comp) = Region I want the MIN test to ignore the value in the adjacent cell in Col C.

Formula To Count MAX Of A Column With Condition
problem is in the same vain as my last thread "Formula to count MIN with condition". In the attached worksheet samples, Col B has competition dates, Col C is time/distance data, Col C (filled down) is the formula that recogises if the time/distance is a PB.

Once a season there is an event "REGION" where I want the formula to ignore the data in Col C adjacent to "REGION". The date of REGION changes every year and could be anywhere in Col B. The first attachment is what the sheet currently looks like and the second is what I want it to record, specifically D11. Even though D10 is a better distance, it would be ignored as it is REGION.

Copy Rows Where Condition In 1 Column Is Met
I'm looking to create a macro that copies a row of data and pastes it within a range when a condition is met. For Example, if A1 of the header Row matches A2 within a Range of A2:A100, I want to Copy the A1 Row and Paste Special Values over the Match in A2. Likewise if the match is contained in Row 100, I would like to Copy from A1 and Paste Special Values into A100.

Copy Values From Column A Based Upon Values In Column B
If values in column B are alike or if only one value exist in column B, I would like to copy values from column A over into columns D and all other columns to the right of D,

Before and after examples are below....

Summing Values In Column Based On Values In Other Column
I have a vba form with three comboboxes. The first combobox contains a list of employees, the 2nd a list of work activities and the third a list containing hours. As each employee enters the data for their name, activity and hours these values are saved to sheet6 starting at A10, B10 and C10. I want to be able to sum the hours for each employee who enters data. So the completed user form could look like the following:

HTML Employee (A) Activity (B) Hours (C)
Joe Swept 1.25
Raked 1
Bill Mowed 2
Cleaned 1
Joe Trimmed 1

So as you can see there could be a blank cell in column
"A" under Joe and Bill and Joe could come back later and
enter more info. What I want on sheet6 say starting at A30 the value
Joe would be entered and in B30 his total hours 3.25. In A31
Bill would be entered and in B31 his total hours 3 and so on for other
employees

Match Values In One Column To Values In Another With A 3rd Column Included
I have about 360 records in Column 1 and less columns in Column 2 (333 in this case). All the values in Column 2 are in Column 1. I need to be able to match those two columns up while keeping the data in Column 3 intact with it's corresponding value in Column 2. FYI I searched and found this thread: ttp://www.excelforum.com/excel-work...n-another.html

But my problem is that I have to tie in the values with column 3 to their corresponding values in column 2. In order to illustrate, I have attached an excel file in which I did the operation manually. (note: all values in column 2 are indeed in column 1 but not in the example as I have shown for the sake of convenience).

Check Column For Unique Entries And Add One Further Condition
I need to count all unique entries in column A (list of names) (this bit I can do!)...however:

Column B gives each name a value of A or B, I need to count all unique names with an A and all unique entries with a B.

eg

Matt A
Matt A
Matt A
Bob A
Tom B
Tom B

This would give a count of 2 for A's and 1 for B's.

Finding The Maximum Value In A Column Based On A Condition
This should be simple to do but I can't figure it out. I have a database that lists operating room numbers in one column and the length of the surgeries performed in those rooms in another column.

I need a formula that will give me the longest OR time for a given room. For example the room numbers are in column A and the OR times are in Column B. I've tried something like

Selecting Rows Based On A Column Condition
I have a worksheet which is created monthly by one of our company's employees which reports results for that months operations and successful audits. These audits are separated into various service lines and on the report which is submitted it takes on the following format:

1|Service Line|Data A|Data B|Etc.
2|___CR_______| Data | Data | Etc.
3|____________| Data | Data | Etc.
4|____________| Data | Data | Etc.
5|____________| Data | Data | Etc.
6|___DD_______| Data | Data | Etc.
7|____________| Data | Data | Etc.
8|___MS_______| Data | Data | Etc.
9|____________| Data | Data | Etc.

What I want to do is use a macro from another sheet that has the effect of: While ServiceLine = "CR" copy the row of that line and move it to my monthly summary workbook, sheet 1. Then when the Service line changes to DD have the macro copy that information to the monthly summary workbook sheet 2, and so on. The problem is, if I test the Service Line column each time it will be an empty string the majority of the time and I'm not sure how to combat that with my If, Do, For, and While statements. As I've thought about it there are two solutions that I can think of: someone knows of a way to test the row only if it has information in it, or if there is a way that I can copy the service line information down until the change in service line so I can test Service Line on each Row.

Delete Rows Based On Condition Of 1 Column
I would like to Use my Excel VBA program to search each row in a csv document for a name (located in a cell ) if the name exists then I would like to delete the entire row.

Whenever I try to do the above in excel, even when I save in csv format the file formating or something gets changed and the file which has to be procesed through another program then process incorectly.

Is there a way to do what I need while retaining the formating which must obviously be changed when the file is opened in excel?

Match Column And Colour Based On Condition
i am trying to do is match 2 columns data based on a condition ie i have data in column "b" of sheet called "balance" data is variable this column has unique indentifiers i want to look at another column and match the uniques identifiers in another sheet if the cell value in column "D" = "please investigate" otherwise do nothing, the (column f) it will be looking up too is in sheet called "hi- port" so if cell value in column "d" of sheet"balace"= "please investigate" match values that are same from colum "b" of sheet "balance" to that of column (f) sheet called "hi-port"

i would like to colour only entire row of sheet called "hi-port" when the data matches to other sheet ie from columns "f " too " b". colour can be any colour
igonre my ranges they are wrong just used as example

Sub start()
Dim oWs As Worksheet
Dim oRngCheck As Range
Dim oCl As Range
Dim cCola As Range
Dim cColb As Range
Dim rngIRd As Range
Dim rngACs As Range
Dim Match As Boolean
Set rngIRd = Range("a1", Range("a65536").End(xlUp)) 'define the rang
Set rngACs = Range("b1", Range("a65536").End(xlUp)) 'define the rang
Set oWs = Worksheets("balance") 'change to suit
'starts at b2 to llow for header row.......................

Copy Rows Of Column Meeting Certain Condition
I Have workbook-1 with 5 cells and 10 rows and in 5 th (E) cell i have value of 1 or 0 .

i want to selectively copy entire rows which has value = 1 in cell(E) and paste these rows into 2 nd row(A2 cell) of workbook-2 using Macro

Copy Records Meeting Condition In Column
I have created a loop to go through a series of records on one spreadsheet, and if it finds a "100.00%" case in column G then it will cut that record, move to another spreadsheet and paste it there. I am so close but I can't seem to get the pasting part to work and I've tried quite a different number of things.

Dim cellPercentage As Variant
Dim cellLocation As Range
Dim x As Integer
Dim found100PercentCases As Range

Copy Rows Matching Condition In Column
On my main worksheet, I have a list of stocks in the "A column", so for example reading down the list would be:

Vodafone
Vodafone
Ericsson
Nokia etc...

I have a seperate worksheet for each different name (about 100), and I wanted to know if there was a way to select all the information relating to the name "vodafone" for example, and to copy and paste that into the worksheet called vodafone. The problem is copying and pasting 100 times would take ages and wanted to know whether there is an easier way using VBA.

Create Unique List From Column With A Condition
I have a test sheet thus :

+ABCDEFG
1Bob11234
2Sally2
3Ann3
4Emily4
5Lexi3
6Bill2
7Josh1
8Jacob2
9Mary3
10Gary4
11

In D2:D11, I want a non-duplicate list of all the "1" names, i.e., Bob and Josh. In E2:E11, a list of all the "2" names (Sally, Bill & Jacob), and so on. Sorted would be nice, but not necessary. When new names and their numbers are added to A1:B200, they need to automatically show up in D2:G:201 as appropriate. I've looked over VLOOKUP, INDEX, MATCH, etc., but all seem to simply return the first and not every match. Maybe I'm just missing it.

Is this possible without VBA? Not that I have any objection to it, but delving into and learning it is a little outside the available time I have now. If it takes VBA I'll address it when I can free up some time.

Format All Rows Where 1 Column Meets Condition
I'm trying to Multi-Select rows in order to format all rows, in "One Step" if the cell in col. "A" = 1. I managed to write the hereunder code but I'm still frastrating because I don't like the first RNG setting command. How can I avoid declaring RNG as row(1) and still be able to run the code. I assume I need some other Range set for RNG which, unfortunately, I could'nt find and the command I used includes row1 in RNG although cell A1 = 5.

Sub Conditional_Multi_Rows_Select()
Set RNG = Rows(1) ' this is my problem
LR = [A65536].End(xlUp).Row
For R = 1 To LR
If Cells(R, 1) = 1 Then
Cells(R, 1).EntireRow.Select
Set RNG = Union(RNG, Selection)
End If
Next
RNG.Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End Sub

I had in mind to use:

Set RNG = Rows(65536)
but this will not make it a "neat" vba code, either.

Exclude Rows Based On Condition In 1 Column
I have a worksheet that I want to export to a csv file. However, there are multiple rows that I want excluded in the export. These lines are recognized by the word "No" in column E. Is this possible?

Hide/Unhide Rows Meeting Condition In Column
In the included sheet I have 5 groups of data with five categories in column A. Current, Plan, Plan Var, Prior, Prior Var. I have included the button “Show Options” that opens the userform I created and gives 5 options. What I want is the user to be able to select any number of these options and then upon “ok” the rows in the sheet that weren’t selected are automatically hidden. If the procedure is completed again and a differen set of options is selected I want it to unhide any hidden ones that were selected and hide any that werent selected.

So if just current is selected the sheet will show 5 rows of current and nothing else. If current and prior are selected it will show current prior current prior current prior... etc.

I have some hide code that I created in the file as well.

Sub NotCurrentHide() ...

Copy Multiple Cells Based On Condition In Column
I need the macro that looks in to say Column H sheet1, if value ="true" then copy cells in column A, B, D, F to Sheet2.

Color Rows For All Cells In 1 Column Meeting Condition
A friend is trying to change an entire row's color based on a specific cell's value in that row. He cannot use conditional formatting. This is the code he's tried, to no avail:

Sub temp()
totalrows = ActiveSheet.UsedRange.Rows.Count
For Row = totalrows To 2 Step -1
If Cells(Row, 25).Value = 4 Then
Rows(Row).Select
Selection.Font.ColorIndex = 3
End If
Next Row
End Sub

Copy Rows From Worksheet To Another Where Column Cells Meet Condition
1. I need a script to retrieve data (member number) from "Search List" worksheet and then to search it in "Members List" worksheet.

2. Once the search result (member number) found, e.g. 00311, it will copy the entire row to the "Only Selected" worksheet.

I have also attached a sample excel for better understanding.

Copy Rows Where Cells In Column Match Condition/Criteria
I did a search to find a question similar to mine and I found this:
Copying data matching a criteria into another spreadsheet

This is exactly the same concept I am trying to accomplish, but don't know how to...at all. I couldn't follow the thread either.

I have a schedule which is constantly being updated (simplier version attached). I want to create a schedule, which will:
1) First ask which region to display the information for (I would like to create the same pop up menu to choose from)
2) Then, it will select only that region's information (all rows & columns) and copy them into a new worksheet and lists it all
-if you go back to the Master List, you can again choose another Region and it will create another new worksheet with that region's information
3) Don't know if this is making things more complicated, but at the bottom of the attached file, there are 3 lines under "Land".

Is it possible to list these under the same spreadsheet as the region it is pertaining to but classify it separately under "Land" (ie leaving space, so it is clear this is separate

Copy Range Rows To Another Sheet Matching Condition In Column
i'm trying to create a macro to find any value <> "" from COLUMN B then copy Bx;Cx;Dx and paste into another worksheet with the name of COLUMN A.

And when the cells Cx or Dx is empty should appear 0 into the other worksheet

for example.
sheet1 ..... cell("dx") is empty
a b c d
w 5 3

the macro would copy 5,3,0 and paste into sheet named "w".

i found this code on internet but it's not quite good for me because it copies the hole row. i tried everything to change that but i couldn't.

[url]
(ORIGINAL CODE)

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

Count Where Text Value In 1 Column & Month Of Dates Meet Condition/Criteria
need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. It’s to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))-COUNTIF(F3:F200,">="&DATE(2008,2,31)).
So now I just need it broken down by training type per month. How many x's in each column for February as an example.

Macro Loop To Extract Specific Values And Min/Max Values From Column/Rows Range
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.

There's 2 main events in this loop (I hope that's the correct terminology):

Input 1) User defines the beginning cell to start the loop. In this case, A2.

Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......

Multiple Ranking: Rank The Values In Column B And Then Rank The Values In Column C
What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK.

ABCD
1Non UKUKRank
2Cat20055
3Dog400101
4Eel200114
5Frog40072
6Goat30023

Multi Condition Format? Lookup And Date Condition Valid For
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.

Capture Unique Values From 1 Column And Populate The Result Into Another Column
I have values in COlumn A, probably about 50K rows, The total unique values on the column A is only about 27. I need to capture all those unique values and paste them in column B. I didn't want to do "Advance filtering" since the workbook has many macros's on it that the user simply click on. Im thingking of creating a loop and compare values already pasted in Column b but then that will take too much time to complete the loop. Is there any easier or yet more efficient way to accomplish this?.

Sum The Values In Cells Based On The Values Other Column
I want to sum the values in cells E2:P110 based on the values column D. The
values in D are formulas resulting in something that appears to match D112 in
some cases. I'm using the following equation:

=SUMIF(D2:D110,D112,E2:P110)

My problem is that D2 :D10 have a formula in it and it's not matching. If
I enter the result of the formula, all is good. How should I deal with this?

Summing Up Values Of Right Column By Looking Up Values In Left
If I have two columns like so with Processor speed in the left and number of processors on the right:

SPEED NO OF CPU
7282
7282
7282
7281
7282
7301
7301
7302
7301
7301
7301
7301
7312
7312
7311
7311

SPEED ranging from 75 to 2000 or so Mhz, as many or as little multiples of each speed. I wish to go through this list using a seperate list of distinct values, and count the total number of CPUs for that SPEED. So, for the extract above I could manually tell you that there are a total of 8 processors running at 730Mhz

It would be nice to have a list of distinct vaues with a total count of CPUs to the right of each SPEED

730 8
731 17
756 9
834 2 etc

Pulling Values From A Column And Reordering Them In Another Column Without Spaces
I have a couple of columns that look like this in basic form:

Column A Column B
A 1
B 1
C 0
D 2
E 1
F 0
G 2

What I want is in a separate worksheet for it to pull the names (A, B, E) that have 1s next to them in column B and put them in a list.

I could do this kinda manually, but how can I create a nice list in a separate column on a separate worksheet just of the names (column A values) where there is a 1 in column B? All I can think to do is vlookup the data and put blanks where its not equal to 1 and then manually delete out the other rows.

Lookup Multiple Values In Same Column With Same Column Heading
Is there a formula to isolate observations in the same column (different values) and also all have the same column heading like the file attached?

Conditional Formatting :: Condition 1 Override Condition 2?
I'm having trouble with the two formulas working in conjuction with each other.

Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))

Condition 2:
=TODAY()>=A1

"Condition 1" seems to override "Condition 2"? Ever see that?

Enter 1 Of 2 Values In A Column Conditional To Another Column
I would like a macro that does this

1. Find the "Customer Number" column by name

2. Find the range of the "Customer Number" column (bottom-most cell with data)

3. Find the column named "Date Shipped"

4. If there are any NON-Blank cells in the column named "Date Shipped", put the word "Sent" in the "Status" column (also needs to be found by name). This macro should stop when it is equal to the bottom-most cell in the "Customer Number" column.

COUNTing Values In Column Two On Criteria In Column One
I have a workbook where I am trying to COUNT the values greater than zero in column "J" based on a selective criteria of column "B".

In other words, if the date in a column "B" cell (B3:B78) is greater than the date in A86, I want to count the number of cells whose corresponding value in the "J" column is greater than "0". And then I want to take this selective COUNT and divide it by a COUNT of all the cells in the range "J3:J78".

So, what I am trying to do is:

FORMULA /COUNT(J3:J78)