Free Microsoft Excel 2013 Quick Reference

# VBA: count the number of values that are greater than another X

Hi,

Let's say I have a variable T = Cells(9,9).Value

I have defined a range called BlowUpChoiceOrder.

Within the following cells:

8 6 7 2 4 9 3 10 1 5
I am trying to find the number of values greater than T.

I have the following but it does not give me the right count.

```
VB:

T = Cells(Row, 18 + K).Value
Set BlowUpChoiceOrder = Sheets("Sheet1").Range(Cells(Row, 8 + K), Cells(Row, 17))
Count = WorksheetFunction.CountIf(BlowUpChoiceOrder, ">" & T)

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
For example, if T = 8, the countif function should give me a result = to 2 as I have 9 and 10 afterwards but it does not give me that.

Any help?

Thanks

### Post your answer or comment

comments powered by Disqus

## Related Results

### Count the number of values in a list that meet certain criteria

I would like to count the number of values that are greater than a cell
reference value. For example I have the following list in a spreadsheet:
242
318
743
426
174
397
108
75
And would like to compare the following numbers to see how many numbers in
the first list are greater than each of the numbers in the list below:
242
218
194
169
145
121
97
73
48
24
in other words there are 4 numbers in the first list greater than 242 in the
second list.
I can do this by the following formula:
=COUNTIF(G8:G17,">=242")
but it means manaually changong the formula each time with the number in the
second list e.g. 242, 218 etc.
I would like to automate this so I don't have to manually make these changes.
How do I do this as I have over 30 lists each with 10 values to compare?

### Count the number of occurrences in two different ranges of data

How do you count the number of occurrences in two different ranges of data, Could someone please help?

The problem is in the attached Excel spreadsheet.

count the number of values that are greater than or equal to 2.1 AND less than or equal to 2.5

For example, doing a visual count will give us the values that are displayed for each column, as seen below in the gray area. But of course, we want a quick, easy and automatic solution!

We've been able to find one way to count the "greater than or equal to 2.1" part, and we're challenging YOU to provide an automated solution for the whole problem.

Please see excel worksheet

### Formulas: Counting the number of cells that lie between 2 di

I have a spreadsheet where I have a range of percentages.

I want to count the number of cells that fall between two values.

For example;

lower value is 28%
upper value is 35%

I know how to use the countif function for a single criteria but am confused about how to incorporate multiple criteria using this function.

How would I make this function work properly.

Also, without using conditional formatting or VBA, is there something that I can type into a cell that will turn the font a certain color. I know in custom formating [] are used with the name of the color inserted in between them.

thanks

### Counting the Number of Values Below or Above Average

Problem:

Counting the number of values in List1 (column A) that are below the range's average, as well as the number of values above average.

Solution:

Use the COUNTIF and AVERAGE functions as shown in the following formulas:
To count values below average:
=COUNTIF(A2:A9,"
To count values above average:
=COUNTIF(A2:A9,">"&AVERAGE(A2:A9))

### Counting the Number of Values Meeting Criteria, but Only for Every 3rd Cell in a Range

Problem:

We want to count the number of values in row 1 that are greater than the criteria specified in cell A4, but only for every 3rd cell in the range.

Solution:

Use the SUMPRODUCT, MOD, and COLUMN functions as shown in the following formula:
=SUMPRODUCT(--(MOD(COLUMN(\$A\$1:\$J\$1)-COLUMN(\$A\$1)+1,3)=0)*(\$A\$1:\$J\$1>\$A\$4))

### Count All Numbers In Range That Are Greater Than The Max Of Another Range

Hello,
I have an inventory spreadsheet that has the counts for my items in columns. This is a one page spreadsheet. I also have set conditional formatting to highlight these cells if the count is over the max. for that item. Is there a way to count the number of highlighted cells in each column? I know there must be an easy answer but I am just not seeing it. Thanks in advance

### Counting number of cells that are not empty

Please I have data in worksheet that I'm working on. I'm supposed to fill in staff details and I've really gone far with the job. I want to know how many staff I have entered their details by counting number of cells that are not empty within a given range in a column. How can I achive this? If on the other hand I want to know the number of staff whose details have not been entered by counting the number of cells that are empty, how will I also do it? The details are in alpha numeric character.

Thanks so much.

### How to count the number of 'values' in a cell

Hi

I am after something that seems simple, but I can't figure out a way to do it.

If I have a single cell containing a list of values seperated by a comma (ie a csv list in a cell), how do I count the number of values. I figure it must be by counting the commas somehow and then adding one as the last doesn't have it, but there is no way to tell how many there will be.

My cell contents look like this
18911, 18801, 18825, 18482, 18941

so I want to have another cell return the number 5.

Does anyone have any ideas?

Regards
Sandi

### Count the number of colored cells in a column

I have a column that has conditional formating applied and want to count the
number of cells that are red or green or any color.

### Counting the number of Selected Fields in a Pivot Table Filter

Hi,

I've created a pivot table that shows YTD information. The YTD date range is generated by selecting multiple months in a pivot table filter.

I have some formulas outside of the pivot table that are dependant upon how many months of data are being displayed.

In the filter, when more than one month is selected, is diplays "Multiple Items."

My question is, is there a way to count the number of months that are selected in the Month filter?

### Formula that evaluates whether the contents of a cell are greater than X but less tha

Can anyone tell me how to write a formula that evaluates whether the contents of a cell are greater than X but less than or equal to Y and return one value if true and another if false?

Thank you in advance!!

### Countif with a changing range

I think I've done this before but I can't remember how. I have a table with
consecutive dates in one column and values in second column. I want to count
the number of values that are greater than a specific number within a given
date range. The date range is to be set by two cells with a start date and
end date.

How can I best do this?

### Counting the Number of Values from Multiple Worksheets that Match Criteria

Problem:

Counting the total number of values from the range A1:B3 on Sheets 1, 2 & 3 that are greater than the criteria specified in cell A2.

Solution:

Use the SUM, COUNTIF, and INDIRECT functions as shown in the following formula:
=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!A1:B3"),">"&A2))

### Count the number of values that fall with in a range

Hello everybody

I have a data like below

```Zipcode
64080	0.4	1	90913
64080	0.4	1	90913
64080	0.4	0	94876
64083	1	1	90913
64083	1	1	90913
64085	1	0	90913
64085	1	0	3462```
So for each value in column1
if column3 value is 1 then
if value in column4 is unique (which has never appeared before for that zipcode) then
if column2 value is >0.75 then
1 should be added under values> 0.75 column
else if column2 value is >0.5 and <=0.75then 1 should be added under values > 0.5 column ....

Similarly for each value in column1 the number of unique values in column4 should be counted and the total number of values should be entered under the following columns

```Thanks,
vb

View Answers

```

### Counting the number of times a range of values appears in a table.

Hey all, hopefully someone can help. This should be easy but is kicking my butt

The reason for this is that it appears that we have some people fudging measurments over the past several years of data and that it may be ongoing. If they are not fudging then we have some external factor seeming to create trends in our data that we have to identify as some of it is too uniform.

I need to find several things and I believe it all hinges on the counting the number of times a list of numbers appears in a table of data. Pivot tables are not working for me on this.

The data is in Column A to Column G in my worksheet
The columns are headed Date, M1, M2, M3, M4, M5, M6
The Date column contains the DAY and DATE both as text. (wednesday, march 16, 1993)
Columns headed M1 through M5 contain the numbers 1 through 80
Column headed M6 contains the numbers 1 through 60
Each row of data contain unique numbers in M1 through M5 (columns B,C,D,E, and F)

The first thing I need to figure out is how to count and output every number in the range 1 to 80 appears in columns M1 through M5. so in the entire range of data in columns headed M1 to M5 how many times does 1 appear, 2 appear, 3 appear, etc all the way through 80.

There are thousands of rows of data (and it grows every day) so doing COUNTIF for each of the 80 numbers as individual entries is not practical.

So the first question is how do I pass the numbers 1 to 80 to the function and out put them to a new column or table?

What i need is to create a table (or set of columns) that say "Measurment X appears" ?? "times" and automatically updates as I enter each new data row to the table. This is for each of the 80 possible numbers.

Next I need to count the number of times that each number 1 through 50 occurs in Column G (headed M6) and that updates automatically as I enter each new data row.

I also have to calculate how often the numbers appear in 'groups'.
Meaning how often does any specific number appear in the same row as any other specific number or numbers.
for example
how often do 1, 34 and 56 appear in the same row, this has to be for all possible 5 number combinations of 1 through 80.
No numbers in columns headed M1 through M5 will be duplicated on the same row, but the value in column headed M6 might duplicate on the same row with a number form M1 to M5. For example M3 might have 24 and M6 might also have 24 on the same row.

Then I have two more calculations to do.
The first is using the DAY values Monday through Sunday (7 days) how often does each of those numbers occur on each day. For example on Mondays the number 15 occured 29 times in the range of data. I need this for every number in M1 to M5 (1 to 80) for every DAY Monday through Sunday. I also need this for MG.

Second, the 'groups' found in the previous calculations have to be checked against the same DAY frequency calculations. For example if a group (1 and 56, and 72) occurs more than ONCE in M1 to M5 how often did it occur on Mon, Tues, Weds, etc.

I appreciate any input I can get as even the first step has me stumped at this point.

### Count Number of Cells conditional on Value and Date

Hi all,

I would like an Excel formula (not a macro) that counts the total number of values that are greater than or less than zero dependent on the date. I am unsure whether this is best done using an IF statement nested with a COUNTIF.

My data is set up as follows: the dates are in cells A3:A3731 â€“ there are many dates that are the same (eg. 31/12/2008 might appear 20 times in that column). The values are in cells G3:G3731 (with no blanks).

Eg.
30/09/2009â€¦â€¦.-0.57
31/01/2010â€¦â€¦.0.04
31/08/2009â€¦â€¦.-0.65
31/08/2009â€¦â€¦.0.27
30/09/2009â€¦â€¦.-0.38

I am thinking of having column H as the column of dates (which I have already), column I as the number of cells in column G that are greater than zero, column J as the number of cells in column G that are less than zero.

eg. I would like the final result as (based on the above example):
........Hâ€¦â€¦â€¦â€¦â€¦I.â€¦..J
31/01/2010â€¦â€¦1â€¦â€¦0
31/08/2009â€¦â€¦1â€¦..1
30/09/2009â€¦â€¦0â€¦â€¦2

Therefore, I need some advice on the formulas that I need to input into cells in column I and J only.

Thank you in advance for all help received.

### Counting the number of characters that match between 2 data sets

First off, long time viewer and first time poster, so thank you to everyone that posts information here. you have all been a tremendous help.

My current task.

I currently have a task of matching 2 sets of data. The data is simply business names from two different sources. I currently remove all special characters and remove blank spaces. This leaves me with letters only to be matched against one another

As an example, the following could be my first data source:
(data source one is my master data and all subsequent lists are matched against the master)

A1-bugerking
B2-harveysltd
A3-dairyqueen

data source two would be:

B1-burgersandkings
B2-harveysltd
B3-diaryqueen

I am currently running the following formula =IF(ISNA(VLOOKUP(LEFT(\$A1,1) & "*",DataSource2!\$B:\$B,1,FALSE)),0,1)

This lets me know if the first character matches. I then run the same formula but to match characters for the second letter of both sets. And another to match the third , and fourth, etc etc.

Currently I use the above formula 20 times, to match up to 20 characters. I then sum the total which gives me the number of characters that match starting from the left until a break appears. As an example the formulas would return the following for the example i provided:

burgerking = 6

harveysltd = 20 (even though the entry is only 10 characters, this formula returns a value of 20. This is actually quite helpful as any entries that have returned a 20 are essentially guaranteed matches)

dairyqueen = 1 (due to the spelling error)

I am thinking there must be a better way.

PS - I am not an advanced user, i have no idea why these formulas work, but they do. I simply found them on this bulletin board. So please keep that in mind when you are judging the mess i created and offering assistance. Thanks

### Count the number of cells that contain text in the number

Column A contains numbers. There are a few cells in Column A that have a "x" place in front of the number. Column B shows whether this item is open or closed. I need a formula that will count the number of cells in column A that contain the letter x.

I would then be able to create a pivot table that would show me the number of x'd items that are open and the number that are closed.

Example:
1098165-02 closed
x100629-01 open
x100629-02 closed
100104-01 closed

I need to show that there are two in the above list that contain the letter x. One is open, One is closed.

Thanks so much for you help.

### How to count the number of "#N/A"

Hi,

I want to count the number of cells that have #N/A in them. The #N/A are the results of formulas that can't find values, not literal strings of "#N/A".

I tried =count(A1:A3000, "#N/A") but it never finds anything. Any ideas?

Roger Bacon

### How do you count the number of values in a row from a seperate sheet?

Hi!

I want to count the number of rows (with a value in them) from a seperate sheet. What is the formula for that?

Example.

I have a colum with 200 values and on a seperate sheet I want to insert a formula that will automatically count the number of entries in that colum. Is there a way to do that? Your help will be greatly appreciated.

Thanks!

### Counting the number of Time!!

Hi,
I have a list of times in column A and in cell b1 I want it to return the number of times that are before 2 O’clock. So I need to count the number times before 2 O’clock.

Thanks

### Count number of values between plus signs in addition calc

I'd like to count the number of values that a user enters in an excel simple
addition calculation.

For example, if the cell has "=17+256", I'd like to get the count of "2".
If the cell has "=101+2+0+65", I'd like to get the count of "4". Is there a
way to parse this 'function' string?

Everything I've tried so far starts *after* the cell value has been
calculated (e.g. it's working with a single value of 273 and a single value
of 168 for the two examples above, respectively).

Thanks,
- Cathy

### In a set of dates, can you count the number of Jan '07 instances?

I have a column of dates where I need to count the number of instances that
fall within Jan 1 to Jan 31 2007. I have tried to use the countif function
and can only count values greater than a value or less than a value, but not
between two values. Is there another way?
--
Linda W

### Formula to count number of days in range which are less than today

Could anyone proivide me with a formula that will allow the following:

I have a range of dates and I would like to count the number of dates that
are less than todays date.

I have inserted =today() for the current date then tried the counif
function, however no joy.

Can anyone help

Thanks

MM

No luck finding an answer? You could always try Google.