# Count Observations Based On Specific Conditions With VBA

Sep 11, 2012
I would like to write a macro that would count observations from a database extract based on specific conditions and generate a report.

My database extract looks like this :

Client name

Info1

Info2

Info3

Bob

aaa

aaa

aaa

[Code] ....

I would like to have an efficient and simple VBA code to count the observations. Something that would store the result of query like those:

# 'client name' = Bob : 2

# 'client name' = Bob AND info1 = aaa : 1

# Info3 = bbb : 2

# 'client name' = Bob and Info1 = aaa OR Info1 = bbb : 2

I am aware of DCOUNT function, but I am not too sure how to translate it in VBA. I also think that using autofilter and counting the visible rows may be subject to bugs.

View 9 Replies
ADVERTISEMENT
May 11, 2006

I have a spreadsheet which is linked to several other worksheets. I have managed to include formulas to count how many cells have numbers between 101 and 5000 by using this formula -

=sum((h2:h500>=101)*(h2:h500<=5000))

but now I want to count the number of cells in another worksheet that are equal to or less than zero. When I use the same formula as above it counts all the blank cells. I have tried using a countblank formula and then deducting this from the result, but unless the other worksheet is open the countblank formula does not work.

View 9 Replies
View Related
Sep 24, 2013

I'm trying to create a calendar overview of transactions from a raw data list. the result should be an amount, based on the number of occurrences of a specific transaction type at a particular date. Thus, the counter shall be based on two conditions. I've tried COUNTIF, INDEX, MATCH and VLOOKUP combined but I don't seem to "go all the way".

View 1 Replies
View Related
Jan 28, 2013

I have an issue log containing (separate) columns with dates, numbers and text.

One column (A) has the status (open/in progress/pending/closed/re-opened)

One column (B) has raise dates

One column (C) has close dates

One column (D) has the type of track (this is text, 3 types)

I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).

After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.

So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".

After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.

View 3 Replies
View Related
Jun 18, 2014

I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.

Code to fill these blank cells automatically with some specific texts based on the attached conditions.

I have attached the workbook for more details. FillCellsTest.xlsmâ€Ž

View 10 Replies
View Related
Aug 9, 2014

I am trying to work with conditional formatting to turn a field a specific color based on two conditions, one of which is variable. The first condition is a list of ongoing events (which can change) and the second condition is a set of known milestones. I want to create a control (check boxes?) that will turn a given field (which has dates in it from another calculation) to either Red, Yellow, or Green.

I was thinking two Dropdown boxes - one for the events and one for the milestones. The user would choose an event, choose the milestone, then choose a checkbox or something to change the color of a field elsewhere in the spreadsheet. Right now, I'm doing this in a very clumsy way by having three checkboxes for each milestone for each event. There are five milestones per event (so 15 checkboxes per event on Sheet2). I will likely have 70+ events so I was hoping for a better way to do this. I've done this for the first three events and need a more efficient way of doing this.

See the attachment : Event Milestones based on Go Date Uploadable.xlsxâ€Ž

View 10 Replies
View Related
Feb 22, 2011

I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.

In excel is best illustrated what I mean!

In total, 5267 lines and need to do this with all. You can do this in excel?

View 4 Replies
View Related
Sep 19, 2013

I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.

I've attached an example sheet : Example email report.xlsx

The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.

So, in the example, the result would be 7.

I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.

View 8 Replies
View Related
Jan 3, 2013

I'm trying to create a formula that tells me the following:

If the item is marked as closed (vs. active), then how many renewals/terminations replacements/etc are there? These are two difference columns in a worksheet.

View 2 Replies
View Related
Nov 25, 2009

I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.

View 3 Replies
View Related
Oct 20, 2009

I'm trying to get 3 formulae for calculating days in drawdown…

Column A has daily dates and Column B has drawdowns which are either a negative number or 0(which means there is no drawdown)…

Every drawdown period starts with a negative number, and as long as the numbers continue negative the streak continues until it is stopped with a zero.

1. Need - Longest period in drawdown….this should basically be the most number of consecutive negative values in Column B (before the streak being interupted with a 0)

2. Days in Current drawdown - this is the most recent streak of negative numbers, in our case the answer is 10 as shown in column F. (hopefully formula detects the date and figures this out)

3, Days in Maximum drawdown - this is the negative streak which had the lowest value…….in our case I believe its about 292.

View 6 Replies
View Related
Aug 9, 2009

I have a range, intervals and need to find out how many observations are in those intervals...

RANGEINTERVALFREQUENCE-0.10773-0.10773-0.11 ?-0.10144-0.11 -0.05 -0.0983-0.05 0.01 -0.085110.01 0.07 -0.0810.07 0.13 -0.078760.13 0.19 -0.07580.19 0.25 -0.071360.25 0.31 -0.070750.31 0.37 -0.06930.37 0.43 -0.068420.43 0.49 -0.068060.49 0.55 -0.067390.55 0.61 -0.060030.61 0.67 -0.059350.67 0.73 -0.057470.73 0.79 -0.05290.79 0.85 -0.052570.85 0.91 -0.05190.91 0.97 -0.051340.97 1.03 -0.050011.03 1.09 -0.048051.09 1.15 -0.046941.15 1.21 -0.046411.21 1.27 -0.045311.27 1.33 -0.045291.33 1.39 -0.045061.39 1.45 -0.045021.45 1.51 -0.044341.51 1.57 -0.043321.57 1.63 -0.042231.63 1.69 -0.041661.69 1.75 -0.041441.75 1.81 -0.040051.81 1.87 -0.038661.87 1.93 -0.038561.93 1.99 -0.037811.99 2.05 -0.037112.05 2.11 -0.036272.11 2.17 -0.033292.17 2.23 -0.032872.23 2.29 -0.032672.29 2.35 -0.031322.35 2.41 -0.029372.41 2.47 -0.029262.47 2.53 -0.028292.53 2.59 -0.028222.59 2.65

View 9 Replies
View Related
May 6, 2009

I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2".

In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.

View 3 Replies
View Related
Nov 17, 2007

see my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.

View 2 Replies
View Related
Nov 1, 2009

i dont understand why this code is not working. i get run time 1004 application or object defined error. basically i want to count column 11 if there is a value in column 2.

Public Sub offloadDoor()

Dim unassigned As Long

unassigned = 0

For rowvar = 18 To 504

If IsEmpty(Sheet2. Cells(rowvar, 11).Value) = False Then

If IsEmpty(Sheet2.Cells(rowvar, 2).Value) = False Then

unassigned = unassigned + 1

End If

End If

Next

Sheet8.Cells("b3").Value = unassigned

End Sub

View 5 Replies
View Related
Jul 9, 2014

I have a column : Customer Number

Customer Number: we have 2 types of customer numbers (12 or 13 alphanumeric)

eg: 446828442414 (12 alphanumeric) or 8144144141442 (13 alphanumeric)

and need them formatted according to number of digits

If 12 alphanumeric: 00<3space>0<3space>00<3space>00<3space>00<3space>000

i.e. : 44 6 82 84 42 414

or if 13 alphanumeric: 000<3space>000<3space>00<6space>00<3space>0<3space>00

i.e.: 814 414 41 41 4 42

suggestion: IF (No. of characters = 12) use 12 alphanumeric space format

or

IF (No. of characters = 13) use 13 alphanumeric space format

View 3 Replies
View Related
Jan 31, 2009

I am looking for help in developing steps in a spreadsheet to calculate a sum based on a few conditions. The basis of my spreadsheet works fine, however I am trying to add the contents of a cell to another by checking other cells.

For example:

Cells A1:A10 contain a numerical sequence (1-10 respectively) to indicate a "Node"

Cells B1:B10 contain a user selected number (1-10) which defines the Node it is fed from. We'll call this "Fed From Node".......ie. If Cell B2=1, that means that Node 2 is fed from Node 1.

Cells C1:C10 contain a value which is input by the user. We'll call this "Amps"

Cells D1:D10 would show the total "Amps" of the current cell added to all others that are fed from same.

What I am ultimately trying to figure out is how to add the "Amps" to each "Node" and show the results in column D.

More specifically if the spreadshet looks like the following: ...

View 9 Replies
View Related
Feb 1, 2013

I have need of VBA code that will check collumn D, starting at cell 7 until cell 206, for when data that is entered starts with 100. When this occurs it will remove all of the data in that same cell except for the last 12 digits.

Here's an example.

When using our barcode scanner to capture a FedEx Tracking number the barcode captures the following:

1001853514360009020200802541997796.

Embedded in that long string of numbers (which are formatted as Text) is the tracking number. Specifically the last 12 numbers are the tracking number. However, FedEx varies this code at times so the last 12 numbers are the tracking number only with the raw data captured by the scanner starts with 100. Until I find additional conditions this VBA code will have to ignore all of data that is captured in collumn D starting at cell 7 until 206.

View 3 Replies
View Related
May 12, 2014

I am trying to use an IF(AND) formula to set conditions where specific values will be counted under certain circumstances.

{=COUNT(IF(AND(Annual_Rate>36000,Annual_Rate

View 7 Replies
View Related
Aug 4, 2005

I have a table in Excel:

The first row is time in years.

The second row is method name,say,"A","B","C".

I want to count the number when the time is less than 5 years AND "A"

method is adopted.

I tried this:

count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.

how to revise the formula?

In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as

countif(C2:Z2,"<5")

View 9 Replies
View Related
Jan 7, 2009

I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it.

I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file).

=SUMPRODUCT((C3:C124=1)*(D3:D124=0))

View 2 Replies
View Related
Jan 22, 2009

I have a worksheet with 3 columns in it. these are entitled "area", "uploaded" and "status". uploaded will be a numerical value and status will either be "awaiting signoff" or "completed" what i need to do is list all of the different areas and add the "uploaded" values together IF the status is completed.

View 4 Replies
View Related
Apr 13, 2007

What formula would I use to count a cell only if it met one of three conditions?

ie only if the cell equalled 1, 2 or 3 but not 4?

View 9 Replies
View Related
Feb 13, 2009

40,000 rows,

Column A is a Port Code . . . always 4 digits

Column B is a 2 digit code representing a mode of of transportation.

I did it the "brute force" way of concatenating the two columns into column C, then sorting and subtotalling column C . . . .

View 9 Replies
View Related
Mar 29, 2007

There are a variety of tabs on this database. These tabs track the large customers and specific brands.

In the “Sales Rep Calendar” tab, I have attempted to calculate how many quotes are established 1) per Month and 2) per OS Sales Rep for 2007.

Using formulas such as: ...

View 6 Replies
View Related
Nov 3, 2009

I have a need for a formula that is able to count the data in column that coresponds to data in another column.

Example work sheet attahced for a quikie look.

View 8 Replies
View Related
Feb 16, 2006

Ive started using the sumproduct function to count multiple conditions which is useful

howveer if i want to count those records in one column that meet a condition and those records in another column that meet anyone of a number of conditions how can i do that?

the only way i can think is like the below

=sumproduct(--((columnA=apple)*((ColumnB<>Red)*(columnB<>Yellow))))

Rather than having to eliminate red and yellow i would like to say is green or blue.

View 14 Replies
View Related
May 20, 2007

I'm working out a schedule for work. Row 1 contains 31 days(columns), Row 2 28 days, Row 3 31 days...and so on for the 12 months of the year.

I've formatted each Friday, Saturday, Sunday and Holiday with color. Fridays are blue, Saturdays are green, Sundays are yellow, and Holidays are red. Monday-Thursday are no color.

Next, I fill in each day with an employee name.

Now the hard part...I want to count the number of times an employee name falls on a Monday-Thursday, Friday, Saturday, Sunday and Holiday. At the bottom of the worksheet I'd like to see something like this:

Jones:

Friday 4 (total number of days jones is in a blue box)

Saturday 5 (...on a green box...and so on...)

Sunday 3

Holiday 2

Monday-Thursday 50

For each employee name. Sounds easy, right? I can't get it to work!

View 14 Replies
View Related
Feb 27, 2014

I am struggling to come up with a formula that will look at one column of data to check for a condition (for example, it says 7. Implement) and then look at another column to see if a second condition is met (this one is a date ie Feb-14) and count those that meet both. The data I wish to check is on a different worksheet if that makes a difference...

View 1 Replies
View Related
Apr 25, 2008

I am trying to count the number of entries in range BH3:BH621 when the cells in range B3:B621 = "Acting" and the range D3:D621 = "Feb"

I can do it with either the B range or the D range, but not both together.

View 9 Replies
View Related