Free Microsoft Excel 2013 Quick Reference

Countif for greater than a cells value Results

Attached is a simple sample file.

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?

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?

Hi all,

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!

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

If your input array is in A21:H21,
=IF(MAX(A21:H21)

I have a range of numbers where the mean is 1.105... and I want to count all
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?

Hi. I'm trying to create a formula that will count how many numbers in a list
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

This will return the value in A1 if it is less than 0. You did not say what
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 your input array is in A21:H21,
=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
>

I have a range of numbers where the mean is 1.105... and I want to count all
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?

Quick question about SUMIF and COUNTIF with multiple criteria: I've attached an example. Basically, I want to include a cell in the sum if it's value is greater than the MINPRICE value and the AVAIL value for its column is equal to 1. Is there any way to write this in one cell as a concise formula, as opposed to writing seven different SUMIF and COUNTIF functions?

Thanks for any help!

I have a formula (AL15) to determine the address of the last column with a value greater than 1. =COUNTIF(C16:AC16,">=1")+2. I then use this column value to aquire the Absolute row "15" and the determined column=(ADDRESS(15,AL15,4)). This returns to me a Column value and row value that I need to use as a refference. How can I convert this returned value to a reference. Thank you for any help

Hi. I'm trying to create a formula that will count how many numbers in a list
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

Hi all, I have a very simple problem but I have spent several hours trying to find the answer. I have really tried to search for the answer but no matter how I formulate my search criteria I always end up with a thousand irrelevant links.

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

Hi forum,

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

I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell
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

Hi,
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

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.

First time poster, sorry if this is hidden somewhere within...

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'm trying to count the number of cells in a column less than a value but greater than 0.

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.