Free Microsoft Excel 2013 Quick Reference

- COUNTIF for greater than a cell's value
- Count the number of values in a list that meet certain criteria
- COUNTIF and SUMIF Question
- VBA: count the number of values that are greater than another X
- Finding Min Cell values excluding zero in alternate columns
- Using COUNTIF to find numbers within a range greater than the mean
- Countif Formula with changing criteria
- RE: 'IF' FORMULA TO CAPTURE CERTAIN CELL VALUES
- RE: Finding Min Cell values excluding zero in alternate columns
- Using COUNTIF to find numbers within a range greater than the mean
- SUMIF, COUNTIF with multiple criteria
- Looking for a solution on how to convert a returned cell address to a cell reference
- Countif Formula with changing criteria
- Countif + "greater than" "cell value" 2
- Count number of cells with a string length greater than X
- Counting Cells With Multiple Criteria on Multiple Sheets
- Countif with a conditional argument
- Count Number of Cells conditional on Value and Date
- Count / CountIF question
- How can I Count # cells in specified range with value range?

In cell C8 I want the user to be able to enter any value. This will be their definition of a "Critical Percent level".

Next, I want cell C9 to calculate how many of the values in the range A2:A6 are equal to or greater than that value.

I thought I could use the COUNTIF function, but I can't get that to work with the "greater than or equal" part of what I want.

I'm guessing this is simple, but I don't know how to solve it.

Can someone help me with this?

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?

I'm trying to use COUNTIF and SUMIF to count and sum values across an array that are greater than a specific value. My formula looks something like: =SUMIF(A2:H230,">B1"). The issue is that it returns zero. If I reference the value in B1 (i.e. ">104.7"), instead of the cell, it returns the numbers I'm looking for. I have the same issue with the COUNTIF function. All cells are formatted as numbers. Is there anyway to reference the cell in the conditional, instead of having to hard code it's value?

Thanks for any help!

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: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.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

Any help?

Thanks

the numbers greater than or equal to the mean by using the calulated mean

cell (whe COUNTIF(range,>=H2223) instead of typing in the mean (whe

COUNTIF(range,>=1.105432789etc). I have to do this for several ranges. Anyway

to get the formula to take the calculated value?

of ten that are greater than a changing number in a cell. The cell changes as

it has a formula attached to it. I used as the criteria >b1, but for some

reason this doesn't work. As this number changes it has to be greater than

whatever the cell value is at that time.

--

Thanks!

Stephen

to do if the cell is greater than 0, so this will return a blank (ie "")

=IF(A1<=0, A1, "")

"PaulH-Oz" wrote:

> Hi All

>

> Please help...

>

> I am trying to create a formula that captures a certain cell's value IF it

> is equal to or lower than 0 (zero). I am also trying to reverse this formula

> for another section.

>

> Can someone please provide a suggestion on this? I have been trying to

> figure this one out for the last four hours and am still having no luck.

>

> I am trying to use both IF and COUNTIF for these formlas. Is this correct

> or should I be using another sum?

>

> Cheers

>

> Paul

=IF(MAX(A21:H21)<=0,"NA",LARGE(A21:H21,COUNTIF(A21:H21,">0")))

should return the smallest positive value (or "NA") if there isn't one. The

logic is to count the number of positive values, and use that as an argument

to the LARGE function. --Bruce

"MichaelC" wrote:

> I have an array that is 1 row high by 16 columns wide.

> Each cell may contain a positive value, or a zero.

>

> I need a formula to find the "Minimum value that is greater than zero" in

> columns 1,3,5,7,9,11,13 and 15.

>

> =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I

> need the minimum value that is greater than zero.

> If I use nested IF functions to exclude zeroes I run foul of the max of 7

> allowed.

> I would greatly appreciate any help and thank you in advance for any offered.

> MichaelC

>

the numbers greater than or equal to the mean by using the calulated mean

cell (where: COUNTIF(range,>=H2223) instead of typing in the mean (where:

COUNTIF(range,>=1.105432789etc). I have to do this for several ranges. Anyway

to get the formula to take the calculated value?

Thanks for any help!

of ten that are greater than a changing number in a cell. The cell changes as

it has a formula attached to it. I used as the criteria >b1, but for some

reason this doesn't work. As this number changes it has to be greater than

whatever the cell value is at that time.

--

Thanks!

Stephen

Enough wining =)! I just wanted to explain why I start a new thread instead of searching existing threads.

So.. I want to use COUNTIF function to count the number of cells in a range where the value of the cell is greater than the value of a referance cell. For example: =COUNTIF(A4:A10;>B6), exept this is not working.

It's easy if I would compare it to a number but now I want to compare the value of a cell with the value of another cell.

Thanks

I'm trying to come up with a formula for the above.

I have a range of cells A1:A100 and each cell can have differing string lengths. I need to count the number of times the string length of each cell is greater than X. Lets say in cell B1 the value of X is stored.

I did try COUNTIF in combination with LEN , but couldn't get this to work

Many thanks

David

is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$R1"),2*(AND("'"&$H$1:$H$39&"'!$E1">"'"&$H$1:$H$39&"' !$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$S1"),2*(AND("'"&$H$1:$H$39&"'!$G1">"'"&$H$1 :$H$39&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$T1"),2*(AND("'"&$H$1:$H$39&"'!$I1" >"'"&$H$1:$H$39&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$U1"),2*(AND("'"&$H$1:$H$3 9&"'!$K1">"'"&$H$1:$H$39&"'!$L1"))))

but it returns a value of zero each time. Clearly there is an error in the formula.

Here is some background:

-- $H$1:$H$39 is a block of cells that has the names of the sheets in the spreadsheet

-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing. In this case, I only want to count (add 1) when R1=2 and E1>F1 or when S1=2 and G1>H1 or when T1=2 and I1>J1 or when U1=2 and K1>L1.

Your help is greatly appreciated.

Eric

I am using a countif function and I'm running into a problem with using a conditional argument as the criteria.

I have a spreadsheet in which a column shows due dates for actions and I wish to count how many actions are past their due date based on comparing the values in the column with a date typed into a cell on a different sheet.

=COUNTIF('In Progress'!K3:K500,"<=Sheet1!B1")

The formula works if I replace the 'less than' sign with a 'greater than' sign and shows the correct number of cells where dates are greater than the date in B1 but just displays a 0 if I put in the 'less than' sign to try and get it to count cells where the date is less than the date in B1.

I hope I'm not missing anything simple but it seems illogical that the formula should work for 'greater than' but not for 'less than'

Any help greatly appreciated!

Thanks

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.

What i'm trying to do is count a column of cells based on a value from another cell.

Shorthand version of my excel sheet...Column B has values ranging anywhere from 7000-150,000

A small group of cells in Column A control the calculations to obtain the numbers within Column B

I would like to take a count of the list, based on numbers greater than say, 50% of cell "A7" (A7/2).

I just can't seem to get the 'criteria' for it to work in the way i'd like xD

I can get the number i desire by manually putting that 50% number into the criteria, but it saves time / effort if i can get it to be based on a cell that will change frequently as is.

I would have thought the function should look like:

=COUNTIF(C:C, 0)

However the operators doesn't seem to be acceptable within this format. They are displayed as a textual output. I've also tried:

=COUNTIF(C:C, 1:21)

and also creating a named label for the acceptable range of values

=COUNTIF(C:C, SOON)

where soon is a table with values 1 through 21

I realise I could do this by specifying a list of COUNTIF functions with each of the individual desired values and returning the sum to my target cell but there's got to be a more elegant solution. What if the range was 40000? I'm out of ideas so I'd appreciate anybody who can help. It doesn't seem like a tough problem but I've no idea as to the solution.

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